Uploaded image for project: 'Apache Trafodion'
  1. Apache Trafodion
  2. TRAFODION-2979

Enhance to_date and support transfering string to timestamp with millseconds

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 2.1-incubating
    • Fix Version/s: None
    • Component/s: sql-general
    • Labels:
      None

      Description

      I know we can transfer a string to timestamp using to_date, to_timestamp or cast(.. as timestamp)

       

      Now I have a string 20160912100706259067 and want to transfer to timestamp with millseconds. The only way I can think is firstly changing the value to 2016-09-12 10:07:06.259067 and then using to_timestamp('2016-09-12 10:07:06.259067'). Is there any better way?

       

      In Oracle, it supports TO_TIMESTAMP('20160912100706259067', 'YYYYMMDDHH24MISSFF6'), it seems we can not support it.

       

      Currently to_date in Trafodion can only support below format,

      • 'YYYY-MM-DD'
      • 'MM/DD/YYYY'
      • 'DD.MM.YYYY'
      • 'YYYY-MM'
      • 'MM/DD/YYYY'
      • 'YYYY/MM/DD'
      • 'YYYYMMDD'
      • 'YY/MM/DD'
      • 'MM/DD/YY'
      • 'MM-DD-YYYY'
      • 'YYYYMM'
      • 'DD-MM-YYYY'
      • 'DD-MON-YYYY'
      • 'DDMONYYYY'
      • 'YYYYMMDDHH24MISS'
      • 'DD.MM.YYYY:HH24.MI.SS'
      • 'YYYY-MM-DD HH24:MI:SS'
      • 'YYYYMMDD:HH24:MI:SS'
      • 'MMDDYYYY HH24:MI:SS'
      • 'MM/DD/YYYY HH24:MI:SS'
      • 'DD-MON-YYYY HH:MI:SS'
      • 'MONTH DD, YYYY, HH:MI'
      • 'DD.MM.YYYY HH24.MI.SS'

       

      We need add more fomat to include millseconds such as 'YYYYMMDDHH24MISSFF6'

      The format of millseconds could be FF [1..9]

       

        Attachments

          Activity

            People

            • Assignee:
              anoopsharma Anoop Sharma
              Reporter:
              Yuan_Liu Yuan Liu
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated: