Uploaded image for project: 'Calcite'
  1. Calcite
  2. CALCITE-2980

Implement the FORMAT clause of the CAST operator

    XMLWordPrintableJSON

    Details

    • Type: Bug
    • Status: In Progress
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: None
    • Labels:
      None

      Description

      SQL:2016 adds an optional FORMAT format clause to the CAST operator. It is a standard way to do what functions like TO_DATE, TO_NUMBER, TO_CHAR, TO_TIMESTAMP have done in an ad hoc way (and with differing specifications among databases).

      Here is an example:

      cast('01-05-2017' as date format 'DD-MM-YYYY')
      

      The following paragraphs are copied from IMPALA-4018, which describes implementing this in Impala. (That case also describes cases where the implementations of TO_TIMESTAMP etc. in Hive, Impala, Oracle and PostgreSQL are not consistent with each other. We should take note as we implement these functions in Calcite.)

      SQL:2016 defines the following datetime templates

      <datetime template> ::=
        { <datetime template part> }...
      <datetime template part> ::=
          <datetime template field>
        | <datetime template delimiter>
      <datetime template field> ::=
          <datetime template year>
        | <datetime template rounded year>
        | <datetime template month>
        | <datetime template day of month>
        | <datetime template day of year>
        | <datetime template 12-hour>
        | <datetime template 24-hour>
        | <datetime template minute>
        | <datetime template second of minute>
        | <datetime template second of day>
        | <datetime template fraction>
        | <datetime template am/pm>
        | <datetime template time zone hour>
        | <datetime template time zone minute>
      <datetime template delimiter> ::=
          <minus sign>
        | <period>
        | <solidus>
        | <comma>
        | <apostrophe>
        | <semicolon>
        | <colon>
        | <space>
      <datetime template year> ::=
        YYYY | YYY | YY | Y
      <datetime template rounded year> ::=
        RRRR | RR
      <datetime template month> ::=
        MM
      <datetime template day of month> ::=
        DD
      <datetime template day of year> ::=
        DDD
      <datetime template 12-hour> ::=
        HH | HH12
      <datetime template 24-hour> ::=
        HH24
      <datetime template minute> ::=
        MI
      <datetime template second of minute> ::=
        SS
      <datetime template second of day> ::=
        SSSSS
      <datetime template fraction> ::=
        FF1 | FF2 | FF3 | FF4 | FF5 | FF6 | FF7 | FF8 | FF9
      <datetime template am/pm> ::=
        A.M. | P.M.
      <datetime template time zone hour> ::=
        TZH
      <datetime template time zone minute> ::=
        TZM
      

      SQL:2016 also introduced the FORMAT clause for CAST which is the standard way to do string <> datetime conversions

      <cast specification> ::=
        CAST <left paren>
            <cast operand> AS <cast target>
            [ FORMAT <cast template> ]
            <right paren>
      <cast operand> ::=
          <value expression>
        | <implicitly typed value specification>
      <cast target> ::=
          <domain name>
        | <data type>
      <cast template> ::=
        <character string literal>
      

      For example:

      CAST(<datetime> AS <char string type> [FORMAT <template>])
      CAST(<char string> AS <datetime type> [FORMAT <template>])
      cast(dt as string format 'DD-MM-YYYY')
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                amaliujia Rui Wang
                Reporter:
                julianhyde Julian Hyde
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: