Skip to main content
The DATE data type stores a year, month, and day.

Syntax

You can express a constant value of type DATE using an interpreted literal, or a string literal annotated with type DATE or coerced to type DATE. CockroachDB also supports using uninterpreted string literals in contexts where a DATE value is otherwise expected. By default, CockroachDB parses the following string formats for dates:
  • YYYY-MM-DD
  • MM-DD-YYYY
  • MM-DD-YY (default)/YY-MM-DD/DD-MM-YY
To change the input format of truncated dates (e.g., 12-16-06) from MM-DD-YY to YY-MM-DD or DD-MM-YY, set the datestyle session variable or the sql.defaults.datestyle cluster setting.
Use ALTER ROLE ALL SET {sessionvar} = {val} instead of the sql.defaults.* cluster settings. This allows you to set a default value for all users for any session variable that applies during login, making the sql.defaults.* cluster settings redundant.

PostgreSQL compatibility

DATE values in CockroachDB are fully PostgreSQL-compatible, including support for special values (e.g., +/- infinity). Existing dates outside of the PostgreSQL date range (4714-11-24 BC to 5874897-12-31) are converted to +/- infinity dates.

Size

A DATE column supports values up to 16 bytes in width, but the total storage size is likely to be larger due to CockroachDB metadata.

Examples

Explicitly typed DATE literal:
String literal implicitly typed as DATE:

Supported casting and conversion

DATE values can be cast to any of the following data types:

See also

Data Types