DATE

On this page

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. To set the datestyle session variable, the datestyle_enabled session variable must be set to true.

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

> CREATE TABLE dates (a DATE PRIMARY KEY, b INT);

> SHOW COLUMNS FROM dates;

  column_name | data_type | is_nullable | column_default | generation_expression |  indices  | is_hidden
--------------+-----------+-------------+----------------+-----------------------+-----------+------------
  a           | DATE      |    false    | NULL           |                       | {primary} |   false
  b           | INT8      |    true     | NULL           |                       | {primary} |   false
(2 rows)

Explicitly typed DATE literal:

> INSERT INTO dates VALUES (DATE '2016-03-26', 12345);

String literal implicitly typed as DATE:

> INSERT INTO dates VALUES ('03-27-16', 12345);

> SELECT * FROM dates;

      a      |   b
-------------+--------
  2016-03-26 | 12345
  2016-03-27 | 12345
(2 rows)

Supported casting and conversion

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

See also

Data Types

Was this helpful?