- 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
load table TAB_1(converted_dt '\n')
from '/home2/iq16/WORK/test.txt'
- Execute the query to see the converted data.
- A date value '00010101', 00990101 were converted to '2001-01-01', '1999-01-01' repectively.
Select * from TAB_1 ;
---------------
2001-01-01
2010-01-01
1999-01-01
0101-01-01
1000-01-01
1989-01-01
SAP IQ automatically converts a string into a date when a date value is expected, even if the year is represented in the string by only two digits.
If the century portion of a year value is omitted, IQ's method of conversion is determined by the NEAREST_CENTURY database option.
The NEAREST_CENTURY database option is a numeric value that acts as a break point between 1900 date values and 2000 date values.
Two digit years less than the NEAREST_CENTURY value are converted to 20yy, while years greater than or equal to the value are converted to 19yy.
If this option is not set, the default setting of 50 is assumed (0 to 49 are in the 21st century, 50 to 99 are in the 20th century).
- 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/san12...
http://help.sap.com/saphelp_iq1608_iqrefso/helpdata/en/a6/42956484f21015a7a0e61656a45f16/content.htm...
[IQ 15.4]
http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1540/doc/html/san12...
HTH
Jerry
