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
CAST(<datetime> AS <char string type> [FORMAT <template>]) CAST(<char string> AS <datetime type> [FORMAT <template>])
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.
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.