Support for SQL:2016 datetime formats (limited formats)

This change introduces support for the SQL:2016 FORMAT clause for CAST which is the most widely used method to perform string to datetime conversions. The change also includes support for a limited list of SQL:2016 datetime formats.

Before upgrade to CDP

Timestamp/date handling and formatting is currently implemented in Hive using the Java SimpleDateFormat patterns, however, this is not what most standard SQL systems use. For example see Oracle and PostgreSQL.

After upgrade to CDP

CDP includes support for datetime format patterns as recommended by the ISO and ANSI SQL:2016 standard for SQL database query language. SQL:2016 also includes the FORMAT clause for CAST function, which is the preferred way to perform string to datetime conversions.

Usage

CAST(<datetime> AS <char string type> [FORMAT <template>])
CAST(<char string> AS <datetime type> [FORMAT <template>])

Examples

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

For more information about these patterns, see CAST...FORMAT with SQL:2016 datetime formats.

For more information about the change, see HIVE-21576.

Action required

None. There is no configuration or feature flag introduced by this change to start using the new SQL standard formats. Legacy functions, such as TO_TIMESTAMP and FROM_TIMESTAMP will continue to follow the SimpleDateFormat patterns and CAST (...FORMAT...) will use the SQL:2016 patterns.