[
https://issues.apache.org/jira/browse/CALCITE-2980?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17657101#comment-17657101
]
Julian Hyde commented on CALCITE-2980:
--------------------------------------
[~tjbanghart], In my
[2980-cast-format|https://github.com/julianhyde/calcite/tree/2980-cast-format]
branch I added
[cast-with-format.iq|https://github.com/julianhyde/calcite/blob/2980-cast-format/core/src/test/resources/sql/cast-with-format.iq]
based on Impala's
[test_cast_with_format.py|https://github.com/apache/impala/blob/master/tests/query_test/test_cast_with_format.py]
as of today. You should pull it into your dev branch. It is almost entirely
commented out, but it passes, and you can uncomment tests as you get them to
pass.
The commits in the branch show the sequence of steps by which I generated the
file. If there are transcription errors as I converted from the Python
original, you should be able to map them back to the original.
> Implement the FORMAT clause of the CAST operator
> ------------------------------------------------
>
> Key: CALCITE-2980
> URL: https://issues.apache.org/jira/browse/CALCITE-2980
> Project: Calcite
> Issue Type: Bug
> Reporter: Julian Hyde
> Assignee: TJ Banghart
> 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.20.10#820010)
