> ## Documentation Index
> Fetch the complete documentation index at: https://docs.cockroachlabs.com/llms.txt
> Use this file to discover all available pages before exploring further.

# DATE

The `DATE` [data type](/docs/stable/data-types.md) stores a year, month, and day.

## Syntax

You can express a constant value of type `DATE` using an [interpreted literal](/docs/stable/sql-constants.md#interpreted-literals), or a string literal [annotated with](/docs/stable/scalar-expressions.md#explicitly-typed-expressions) type `DATE` or [coerced to](/docs/stable/scalar-expressions.md#explicit-type-coercions) type `DATE`.

CockroachDB also supports using uninterpreted [string literals](/docs/stable/sql-constants.md#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](/docs/stable/set-vars.md) or the `sql.defaults.datestyle ` [cluster setting](/docs/stable/cluster-settings.md).

> [!NOTE]
>
> Use [`ALTER ROLE ALL SET {sessionvar} = {val}`](/docs/stable/alter-role#set-default-session-variable-values-for-all-users) instead of the `sql.defaults.*` [cluster settings](/docs/stable/cluster-settings.md). This allows you to set a default value for all users for any [session variable](/docs/stable/set-vars.md) that applies during login, making the `sql.defaults.*` cluster settings redundant.

## PostgreSQL compatibility

`DATE` values in CockroachDB are fully [PostgreSQL-compatible](https://www.postgresql.org/docs/current/datatype-datetime.html), 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

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> CREATE TABLE dates (a DATE PRIMARY KEY, b INT);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> 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:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO dates VALUES (DATE '2016-03-26', 12345);
```

String literal implicitly typed as `DATE`:

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> INSERT INTO dates VALUES ('03-27-16', 12345);
```

```sql theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
> SELECT * FROM dates;
```

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

## Supported casting and conversion

`DATE` values can be [cast](/docs/stable/data-types.md#data-type-conversions-and-casts) to any of the following data types:

| Type | Details |
| - | - |
| `DECIMAL` | Converts to number of days since the Unix epoch (Jan. 1, 1970). |
| `FLOAT` | Converts to number of days since the Unix epoch (Jan. 1, 1970). |
| `TIMESTAMP` | Sets the time to 00:00 (midnight) in the resulting timestamp. |
| `INT` | Converts to number of days since the Unix epoch (Jan. 1, 1970). |
| `STRING` | –– |

## See also

[Data Types](/docs/stable/data-types.md)