[jira] [Created] (CALCITE-2980) Implement the FORMAT clause of the CAST operator (added in SQL:2016)

["Julian Hyde (JIRA)" <ji...@apache.org>]

Julian Hyde created CALCITE-2980:
------------------------------------

             Summary: Implement the FORMAT clause of the CAST operator (added in SQL:2016)
                 Key: CALCITE-2980
                 URL: https://issues.apache.org/jira/browse/CALCITE-2980
             Project: Calcite
          Issue Type: Bug
            Reporter: Julian Hyde


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:
{code:java}
cast('01-05-2017' as date format 'DD-MM-YYYY')
{code}
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
{noformat}
<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
{noformat}
SQL:2016 also introduced the {{FORMAT}} clause for {{CAST}} which is the standard way to do string <> datetime conversions
{noformat}
<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>
{noformat}
For example:
{noformat}
CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])
cast(dt as string format 'DD-MM-YYYY')
{noformat}



--
This message was sent by Atlassian JIRA
(v7.6.3#76005)