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

SAP IQ timestamp conversion to varchar wrong resulting format

    XMLWordPrintable

    Details

      Description

      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

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                shawkins Steven Hawkins
                Reporter:
                jstastny Jan Stastny
              • Votes:
                0 Vote for this issue
                Watchers:
                3 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved: