Skip to Content
Author's profile photo Jong-kil Park
Jong-kil Park
November 11, 2014 3 minute read

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
  00010101
  00100101
                           00990101
                           01010101
  10000101
  19890101
      • Load table statement
  truncate table TAB_1 ;
  load table TAB_1(converted_dt ‘\n’)
  from ‘/home2/iq16/WORK/test.txt’
  quotes off
  escapes off;
  commit;
    • 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
  • Cause
        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/san1281565160724.html
          http://help.sap.com/saphelp_iq1608_iqrefso/helpdata/en/a6/42956484f21015a7a0e61656a45f16/content.htm?frameset=/en/a8/90e66f84f210158e8f869393336af4/frameset.htm&current_toc=/en/a8/90e66f84f210158e8f869393336af4/plain.htm&node_id=379

          [IQ 15.4]
          http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc00801.1540/doc/html/san1281565160724.html

HTH

Jerry

Assigned Tags

      Be the first to leave a comment
      You must be Logged on to comment or reply to a post.