DATE data type

Use the DATE data type to store date values.

Use the DATE data type to store date values. The DATE type is supported for HBase, Text, Avro, and Parquet.

Range:

0001-01-01 to 9999-12-31

Literals and expressions:

The DATE literals are in the form of DATE'YYYY-MM-DD'. For example, DATE '2013-01-01'

Parquet and Avro considerations:

Parquet and Avro use DATE logical type for dates. The DATE logical type annotates an INT32 that stores the number of days from the Unix epoch, January 1, 1970. This representation introduces an interoperability issue between Impala and older versions of Hive:

If Hive versions lower than 3.1 wrote dates earlier than 1582-10-15 to a Parquet or Avro table, those dates will be read back incorrectly by Impala and vice versa. In Hive 3.1 and higher, this is no longer an issue.

Explicit casting between DATE and other data types:

DATE type can only be converted to/from DATE, TIMESTAMP, or STRING types as described below.


Cast from	Cast to	Result
`TIMESTAMP`	`DATE`	The date component of the `TIMESTAMP` is returned, and the time of the day component of the `TIMESTAMP` is ignored.
`STRING`	`DATE`	The `DATE` value of `yyyy-MM-dd` is returned. The `STRING` value must be in the `yyyy-MM-dd` or `yyyy-MM-dd HH:mm:ss.SSSSSSSSS` pattern. If the time component is present in `STRING`, it is silently ignored. If the `STRING` value does not match the above formats, an error is returned.
`DATE`	`TIMESTAMP`	The year, month, and day of the `DATE` is returned along with the time of day component set to `00:00:00`.
`DATE`	`STRING`	The `STRING` value, `'yyyy-MM-dd'`, is returned.

Implicit casting between DATE and other types:

Implicit casting is supported:

From STRING to DATE if the source STRING value is in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss.SSSSSSSSS pattern.
From DATE to TIMESTAMP.

Added in:

The DATE type is available in Impala 3.3 and higher.

Kudu considerations:

You can read and write DATE values to Kudu tables. For example:


create table kudu_date_key (fdatekey date primary key, val string)
stored as kudu
insert into kudu_date_key values (DATE '1970-01-01', 'Unix epoch'), (DATE '2019-12-12', 'today')