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

# DATE

export const InternalLink = ({version, path = "", children, ...props}) => {
  let detectedVersion = version || "stable";
  if (typeof window !== 'undefined' && !version) {
    const match = window.location.pathname.match(/\/docs\/([^/]+)/);
    if (match) {
      detectedVersion = match[1];
    }
  }
  const normalizedPath = path.startsWith("/") ? path.slice(1) : path;
  return <a href={`/docs/${detectedVersion}/${normalizedPath}`} {...props}>
      {children}
    </a>;
};

The `DATE` <InternalLink path="data-types">data type</InternalLink> stores a year, month, and day.

## Syntax

You can express a constant value of type `DATE` using an <InternalLink path="sql-constants#interpreted-literals">interpreted literal</InternalLink>, or a string literal <InternalLink path="scalar-expressions#explicitly-typed-expressions">annotated with</InternalLink> type `DATE` or <InternalLink path="scalar-expressions#explicit-type-coercions">coerced to</InternalLink> type `DATE`.

CockroachDB also supports using uninterpreted <InternalLink path="sql-constants#string-literals">string literals</InternalLink> 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` <InternalLink path="set-vars">session variable</InternalLink> or the `sql.defaults.datestyle` <InternalLink path="cluster-settings">cluster setting</InternalLink>.

<Note>
  Use <InternalLink path="alter-role#set-default-session-variable-values-for-all-users">`ALTER ROLE ALL SET {sessionvar} = {val}`</InternalLink> instead of the `sql.defaults.*` <InternalLink path="cluster-settings">cluster settings</InternalLink>. This allows you to set a default value for all users for any <InternalLink path="set-vars">session variable</InternalLink> that applies during login, making the `sql.defaults.*` cluster settings redundant.
</Note>

## PostgreSQL compatibility

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

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

```text theme={"theme":{"light":"catppuccin-mocha","dark":"catppuccin-mocha"}}
      a      |   b
-------------+--------
  2016-03-26 | 12345
  2016-03-27 | 12345
(2 rows)
```

## Supported casting and conversion

`DATE` values can be <InternalLink path="data-types#data-type-conversions-and-casts">cast</InternalLink> 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

<InternalLink path="data-types">Data Types</InternalLink>