Uploaded image for project: 'Teiid'
  1. Teiid
  2. TEIID-5326

SAP IQ timestamp conversion to varchar wrong resulting format

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • 10.3, 10.2.1, 8.12.13.6_4, 10.1.4
    • 8.12.13.6_4
    • None
    • None

      Following Teiid query:

      SELECT TimeStampValue, LOWER(TimeStampValue) FROM BQT1.SmallA
      

      is pushed as:

      SELECT g_0."timestampvalue" AS c_0, lcase(stuff(stuff(convert(varchar, g_0."timestampvalue", 102), 5, 1, '-'), 8, 1, '-')+convert(varchar, g_0."timestampvalue", 8)) AS c_1 FROM "bqt-server"."dvqe"."SmallA" AS g_0
      

      and returns the string representation of timestamp as:
      2000-01-0100:00:00
      2000-01-0100:00:01
      ...
      Notice the missing space between date and time part.

      SAP IQ is capable of converting the value implicitly, e.g. when I issue query

      SELECT TimeStampValue, LOWER(TimeStampValue) FROM SmallA
      

      directly against SAP IQ instance, I get following string format:
      2000-01-01 00:00:00.000
      2000-01-01 00:00:01.000
      ....

      The same results are returned if I use an explicit convert like:

      SELECT TimeStampValue, LOWER(CONVERT(varchar,TimeStampValue,121)) FROM SmallA
      

      For more formatting options see SyBooks Online

              rhn-engineering-shawkins Steven Hawkins
              jstastny@redhat.com Jan Stastny
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Created:
                Updated:
                Resolved: