Date conversion behavior in IQ
- IQ converts a date value ‘00000101’ to ‘2001-01-01’, while it does a date value ‘0099-01-01’ to ‘1999-01-01’ unexpectedly.
- This commonly happens in SAP SYBASE IQ 15.2, 15.3, 15.4, 16.0.
- The steps for reproducing this are as follows.
- Create a table which contains a DATE data type.
- create table TAB_1(converted_dt date) ;
- Load data to the table
- Flat file
- Create a table which contains a DATE data type.
- Load table statement
- Execute the query to see the converted data.
- A date value ‘00010101’, 00990101 were converted to ‘2001-01-01’, ‘1999-01-01’ repectively.
- Cause
- Resolution
- Specify a date format in the column spec of the load table statement.
load table TAB_11(converted_dt date(‘yyyymmdd’), filler(‘\n’))
from ‘/home2/iq16/WORK/test.txt’
quotes off
escapes off;
commit;
=>
converted_dt
—————
0001-01-01
0010-01-01
0099-01-01
0101-01-01
1000-01-01
1989-01-01
- Make a date value with seperator of year, month and day, for example ‘0001-01-01’ instead of ‘00010101’.
- You can see this information in manual pages below.
[IQ 16.0]
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1604/doc/html/san1281565160724.html
http://help.sap.com/saphelp_iq1608_iqrefso/helpdata/en/a6/42956484f21015a7a0e61656a45f16/content.htm?frameset=/en/a8/90e66f84f210158e8f869393336af4/frameset.htm¤t_toc=/en/a8/90e66f84f210158e8f869393336af4/plain.htm&node_id=379
HTH
Jerry