Frank Schimmelpfennig wrote: > > Hi Elke, > > thanks for your investigations! > > >For some (unknown) reason the column create_date in table calling_modules > is not stored as it should be. There is one leading byte missing. > That is interesting. This column (and another column effected in a second > table; also of data type timestamp) is filled up via default SQL function > TIMESTAMP (-> DDL: ...CREATE_DATE TIMESTAMP NOT NULL DEFAULT > TIMESTAMP...). > > >If one selects this table directly, this should be visible (or resulting > an error). > Yes, selecting this column via SQL Studio shows blank fields, when using > the zoom window one gets "[MySQL MaxDB][SQLOD32 DLL] General error". > > >Unfortunately I do not know which version of kernel was used in > September, > when the wrong data was included and which statement/client was used for > it. > The former version was v7.5.00.26. But then we upgraded to v7.6.00.12 at > the 26-AUG-2005. So, the corrupted inserts were done with v7.6.00.12! > > >This means two things: the data in THIS database/table has to be changed > [...] update .. create_date = '2' || chr(create_date) > I tried an update (via SQL Studio): > > update calling_modules > set create_date='2'||chr(create_date) > // > result: Datetime field overflow;-3050 POS(1) Invalid timestamp format:ISO > Looking through the db parameters I found DATE_TIME_FORMAT=INTERNAL. As > the > chr(create_date) function gives the timestamp rudiment in a format similar > to ISO (-> YYYY-MM-DD HH:MM:SS.MMMMMM; the first character missing, but an > additional one attached at the end), I modified the update statement: > update calling_modules > set create_date='2' > ||substr(chr(create_date),1,3) > ||substr(chr(create_date),5,2) > ||substr(chr(create_date),8,2) > ||substr(chr(create_date),11,2) > ||substr(chr(create_date),14,2) > ||substr(chr(create_date),17,2) > ||substr(chr(create_date),20,6) > // > Unfortunately this also does not work: Datetime field overflow;-3050 > POS(1) > Invalid timestamp format:ISO > > Is there any possibility to give the Date-Time-Format in an insert/ update > statement? If there is no better way, perhaps I have to re-create this > create_date column(s; -> two affected tables). > > >that the same table in another database, filled with the same values may > or may not be correct > You were right with this guess! Unfortunately there are other DBs that > have > this problem (-> same tables, same values). > > kind regards > Frank
It is always the same problem with SQL Studio. Internally it uses ODBC. As ODBC has its own date_and_time_format the kernel's installation parameter DATE_TIME_FORMAT will not be taken into account. ODBC's format, looking like ISO is used. You cannot change the format when using ODBC (internally or explicitly). Therefore your > set create_date='2' > ||substr(chr(create_date),1,3) > ||substr(chr(create_date),5,2) > ||substr(chr(create_date),8,2) > ||substr(chr(create_date),11,2) > ||substr(chr(create_date),14,2) > ||substr(chr(create_date),17,2) > ||substr(chr(create_date),20,6) Has to be extended by some || '-' resp. || ' ' resp. || ':' and || '.' to form the ODBC (ISO) format. Or it has to be changed to '2' || substr(chr(create_date), 1, 25), which seems to be easier. Elke SAP Labs Berlin -- MaxDB Discussion Mailing List For list archives: http://lists.mysql.com/maxdb To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
