DATE type

Applies to: check marked yes Databricks SQL check marked yes Databricks Runtime

Represents values comprising values of fields year, month, and day, without a time-zone.

Syntax 

DATE

Limits

The range of dates supported is June 23 -5877641 CE to July 11 +5881580 CE.

Literals 

DATE dateString

dateString
{ '[+|-]yyyy[...]' |
  '[+|-]yyyy[...]-[m]m' |
  '[+|-]yyyy[...]-[m]m-[d]d' |
  '[+|-]yyyy[...]-[m]m-[d]d[T]' }

  • + or -: An option sign. - indicates BCE, + indicates CE (default).

  • yyyy[...]: Four digits that represent a year.

  • [m]m: A one or two digit month between 01 and 12.

  • [d]d: A one or two digit day between 01 and 31.

The prefix DATE is case insensitive.

If the literal does not represent a proper date Databricks raises an error.

Examples 

> SELECT DATE'0000';
  0000-01-01

> SELECT DATE'2020-12-31';
  2020-12-31

> SELECT DATE'2021-7-1T';
  2021-07-01

> SELECT cast('1908-03-15' AS DATE)
  1908-03-15

> SELECT DATE'-10000-01-01'
  -10000-01-01