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.