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

["Rui Wang (Jira)" <ji...@apache.org>]

    [ https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17041661#comment-17041661 ] 

Rui Wang commented on CALCITE-2980:
-----------------------------------

[~danny0405] maybe I could take this Jira over if you don't have a plan to implement recently.

> 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
>            Assignee: Danny Chen
>            Priority: Major
>
> 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
(v8.3.4#803005)