---
title: "make_date"
slug: "dev-database-reference-sql-functions-date-time-make-date"
updated: 2026-10-30T18:19:01Z
published: 2026-10-30T18:19:01Z
canonical: "docs.vastdata.com/dev-database-reference-sql-functions-date-time-make-date"
---

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

# make_date

*Applies to VAST Cluster 5.5.*

*[Functions](/develop/docs/dev-database-reference-sql-functions-index) › [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time) › Building values.* Builds a date from its year, month and day, from a struct holding them, or from a number of days since 1970-01-01.

## Syntax

```
make_date(year, month, day)
make_date({'year': year, 'month': month, 'day': day})
make_date(days)
```

| Argument | Type | Description |
| --- | --- | --- |
| `year`, `month`, `day` | An integer | The parts of the date. Strings of digits are converted. |
| A struct | `STRUCT(year BIGINT, month BIGINT, day BIGINT)` | The same parts, as fields named `year`, `month` and `day`. |
| `days` | `INTEGER` | The number of days since 1970-01-01. Negative numbers count back. |

**Returns:** `DATE`.

## Set up the examples

The examples connect with the ADBC driver, as in [VAST Query Engine (ADBC)](/develop/docs/dev-database-integrations-adbc), and work in a schema called `docs_sql_fn_date_time_make_date`. Set `QE_ENDPOINT`, `AWS_ACCESS_KEY_ID`, `AWS_SECRET_ACCESS_KEY` and `VASTDB_BUCKET` ([SQL reference](/develop/docs/dev-database-reference-sql-index#example-connect-and-use-a-transaction)), and run the blocks in order in one Python session. Below each block is the output you should see.

This block connects, defines the `run()` and `plan()` helpers, and creates a table of five orders. Each has a date (`d`), the same date as separate parts (`y`, `m`, `dd`) and a day number (`days`, days since 1970-01-01). Row 4 has no values:

```
import os
import re

import adbc_driver_manager
import adbc_driver_manager.dbapi as dbapi
import adbc_driver_vastdb

BUCKET = os.environ["VASTDB_BUCKET"]
SCHEMA = f'"{BUCKET}/docs_sql_fn_date_time_make_date"'

conn = dbapi.connect(
    driver=adbc_driver_vastdb.get_driver_path(),
    db_kwargs={
        "vast.db.endpoint": os.environ["QE_ENDPOINT"],
        "vast.db.access_key": os.environ["AWS_ACCESS_KEY_ID"],
        "vast.db.secret_key": os.environ["AWS_SECRET_ACCESS_KEY"],
    },
    autocommit=True,
)

def server_message(e):
    """The server's message in a driver error, without the request ID or the echoed statement."""
    text = re.sub(r"\[req=0x[0-9a-f]+\]: ", "", str(e))
    found = (re.search(r'message: "(.*?)", request_id', text, re.S)
             or re.search(r"description: (.*)\)", text, re.S))
    message = found.group(1) if found else text.strip().splitlines()[-1]
    message = re.sub(r"^Plan query: '.*?' failed with: ", "", message, flags=re.S)
    message = re.split(r"\\n|\n", message)[0].replace('\\"', '"')
    return message.replace(BUCKET, "<bucket>")

def run(sql):
    """Run one statement. Print the rows it returns, or the error."""
    try:
        with conn.cursor() as cur:
            cur.execute(sql)
            rows = cur.fetchall() if cur.description else []
    except (adbc_driver_manager.Error, OSError) as e:
        print(f"{type(e).__name__}: {server_message(e)}")
    else:
        print(rows)

def plan(sql):
    """Print the plan without its ~N Rows lines: the table's row count, which can still be 0 just after an insert."""
    with conn.cursor() as cur:
        cur.execute("EXPLAIN " + sql)
        steps = cur.fetchall()[0][1]
    print("\n".join(line for line in steps.splitlines() if "Rows" not in line))

TABLE = f"{SCHEMA}.orders"
run(f"CREATE SCHEMA IF NOT EXISTS {SCHEMA}")
run(f"CREATE TABLE IF NOT EXISTS {TABLE} (id BIGINT, d DATE, y BIGINT, m BIGINT, dd BIGINT, days INTEGER)")
run(f"INSERT INTO {TABLE} VALUES "
    "(1, DATE '2026-02-28', 2026, 2, 28, 20512), "
    "(2, DATE '2026-03-01', 2026, 3, 1, 20513), "
    "(3, DATE '2024-12-30', 2024, 12, 30, 20087), "
    "(4, NULL, NULL, NULL, NULL, NULL), "
    "(5, DATE '2024-02-29', 2024, 2, 29, 19782)")
```

```
[]
[]
[(5,)]
```

## What it does

`make_date` builds a date in one of three ways:

- **From the year, month and day:** `make_date(2024, 2, 29)` is 2024-02-29.
- **From a struct** with the fields `year`, `month` and `day`: `make_date({&apos;year&apos;: 2024, &apos;month&apos;: 2, &apos;day&apos;: 29})` is the same date. The fields must have exactly those names.
- **From a day number,** the days since 1970-01-01: `make_date(0)` is 1970-01-01, `make_date(-1)` is 1969-12-31 and `make_date(20000)` is 2024-10-04.

The parts must make a real date. 29 February exists only in leap years (2024 and 2000, not 2023 or 1900), a month is 1 to 12, and a day can't pass the end of its month. Anything else fails with `Date out of range`, followed by the parts as given, such as `2023-2-29`.

Years before 1 count back as BC: year -44 is 45 BC. `CAST(make_date(-44, 3, 15) AS VARCHAR)` is `&apos;0045-03-15 (BC)&apos;`.

## NULLs and edge cases

| Input | Result |
| --- | --- |
| `NULL` in any argument, or a `NULL` day number or struct | `NULL` |
| `make_date(2024, 2, 29)`, `make_date(2000, 2, 29)` | 2024-02-29, 2000-02-29 |
| `make_date(&apos;2026&apos;, &apos;2&apos;, &apos;28&apos;)` (strings of digits) | 2026-02-28 |
| `make_date(2023, 2, 29)`, `make_date(2026, 13, 1)`, `make_date(2026, 4, 31)` | Error: `Date out of range: 2023-2-29` (and so on) |
| `CAST(make_date(10000, 1, 1) AS VARCHAR)` | `&apos;10000-01-01&apos;` |
| `CAST(make_date(2147483647) AS VARCHAR)` | `&apos;infinity&apos;` |
| `make_date(2026.7, 2.2, 28.5)` (decimals) | Error: `No function matches the given name and argument types &apos;make_date(DECIMAL(5,1), DECIMAL(2,1), DECIMAL(3,1))&apos;` |
| `make_date(CAST(20000 AS BIGINT))` | Error: `No function matches the given name and argument types &apos;make_date(BIGINT)&apos;`; the day number must be an `INTEGER` |
| `make_date({&apos;year&apos;: 2024, &apos;month&apos;: 2})` | Error: `No function matches the given name and argument types`: the struct needs all three fields |
| `make_date(&apos;abc&apos;, 2, 29)` | Error: `Could not convert string &apos;abc&apos; to INT64` |

The same cases, as the cluster returns them. They use the helpers from [Set up the examples](/develop/docs/dev-database-reference-sql-functions-date-time-make-date#set-up-the-examples):

```
run("SELECT make_date(NULL, 2, 29), make_date(2026, 1, NULL), make_date(NULL::INTEGER), "
    "make_date(2024, 2, 29), make_date(2000, 2, 29), make_date('2026', '2', '28')")
run("SELECT make_date(2023, 2, 29)")
run("SELECT make_date(2026, 13, 1)")
run("SELECT make_date(2026, 4, 31)")
run("SELECT CAST(make_date(10000, 1, 1) AS VARCHAR), CAST(make_date(2147483647) AS VARCHAR), "
    "CAST(make_date(-44, 3, 15) AS VARCHAR)")
run("SELECT make_date(2026.7, 2.2, 28.5)")
run("SELECT make_date(CAST(20000 AS BIGINT))")
run("SELECT make_date({'year': 2024, 'month': 2})")
run("SELECT make_date('abc', 2, 29)")
```

```
[(None, None, None, datetime.date(2024, 2, 29), datetime.date(2000, 2, 29), datetime.date(2026, 2, 28))]
OSError: Date out of range: 2023-2-29
OSError: Date out of range: 2026-13-1
OSError: Date out of range: 2026-4-31
[('10000-01-01', 'infinity', '0045-03-15 (BC)')]
InternalError: Binder Error: No function matches the given name and argument types 'make_date(DECIMAL(5,1), DECIMAL(2,1), DECIMAL(3,1))'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'make_date(BIGINT)'. You might need to add explicit type casts.
InternalError: Binder Error: No function matches the given name and argument types 'make_date(STRUCT("year" INTEGER, "month" INTEGER))'. You might need to add explicit type casts.
OSError: Could not convert string 'abc' to INT64
```

## Examples

Build each order's date from its parts, from a struct and from its day number. All three give the date in `d`:

```
run(f"SELECT id, d, make_date(y, m, dd), make_date({{'year': y, 'month': m, 'day': dd}}), make_date(days) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, datetime.date(2026, 2, 28), datetime.date(2026, 2, 28), datetime.date(2026, 2, 28), datetime.date(2026, 2, 28)), (2, datetime.date(2026, 3, 1), datetime.date(2026, 3, 1), datetime.date(2026, 3, 1), datetime.date(2026, 3, 1)), (3, datetime.date(2024, 12, 30), datetime.date(2024, 12, 30), datetime.date(2024, 12, 30), datetime.date(2024, 12, 30)), (4, None, None, None, None), (5, datetime.date(2024, 2, 29), datetime.date(2024, 2, 29), datetime.date(2024, 2, 29), datetime.date(2024, 2, 29))]
```

The first and the last day of each order's month. The last day comes from [last_day](/develop/docs/dev-database-reference-sql-functions-date-time-last-day):

```
run(f"SELECT id, make_date(year(d), month(d), 1), make_date(year(d), month(d), day(last_day(d))) "
    f"FROM {TABLE} ORDER BY id LIMIT 10")
```

```
[(1, datetime.date(2026, 2, 1), datetime.date(2026, 2, 28)), (2, datetime.date(2026, 3, 1), datetime.date(2026, 3, 31)), (3, datetime.date(2024, 12, 1), datetime.date(2024, 12, 31)), (4, None, None), (5, datetime.date(2024, 2, 1), datetime.date(2024, 2, 29))]
```

Keep the orders placed on a given date. The bound is built with `make_date`:

```
run(f"SELECT id FROM {TABLE} WHERE d = make_date(2026, 3, 1) ORDER BY id LIMIT 10")
```

```
[(2,)]
```

## How it runs in VAST

The Query Engine runs on the cluster's CNodes, and a query on a table starts with a scan in VAST DataBase (`VAST_SCAN`). VAST DataBase can evaluate a comparison of a column with a constant, but not a function of a column:

- **On constants,** such as `make_date(2026, 3, 1)`, the Query Engine computes the date once, before the scan. `d = make_date(2026, 3, 1)` is then a comparison of a column with a constant, and it's pushed down to VAST DataBase: the plan has no `FILTER` step.
- **On columns,** in the `SELECT` list, the scan reads the columns and the Query Engine computes the date for each row it returns. In `WHERE`, the scan sends the columns for every row, and a `FILTER` step in the Query Engine computes `make_date()` and keeps the rows that match.

The two plans, one after the other:

```
plan(f"SELECT id FROM {TABLE} WHERE d = make_date(2026, 3, 1)")
plan(f"SELECT id FROM {TABLE} WHERE make_date(year(d), month(d), 1) = DATE '2026-03-01'")
```

```
┌───────────────────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│      Projections: id      │
│                           │
└───────────────────────────┘
┌───────────────────────────┐
│         PROJECTION        │
│    ────────────────────   │
│             #1            │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│           FILTER          │
│    ────────────────────   │
│   (make_date("year"(d),   │
│ "month"(d), 1) = '2026-03 │
│        -01'::DATE)        │
│                           │
└─────────────┬─────────────┘
┌─────────────┴─────────────┐
│         VAST_SCAN         │
│    ────────────────────   │
│    Function: VAST_SCAN    │
│                           │
│        Projections:       │
│             d             │
│             id            │
│                           │
└───────────────────────────┘
```

## Make it faster

- **Build the bound, not the column.** To find the orders of March 2026, write `d &gt;= make_date(2026, 3, 1) AND d &lt; make_date(2026, 4, 1)`, which is pushed down, rather than `make_date(year(d), month(d), 1) = DATE &apos;2026-03-01&apos;`, which is evaluated row by row in a `FILTER` step.
- **Store a `DATE`, not its parts.** If the table holds year, month and day columns, a filter on a date built from them always runs in the Query Engine. A `DATE` column can be compared with a constant in VAST DataBase.

```
run(f"SELECT id FROM {TABLE} WHERE d >= make_date(2026, 3, 1) AND d < make_date(2026, 4, 1) ORDER BY id LIMIT 10")
```

```
[(2,)]
```

## Limitations and errors

- **One bad row fails the query.** If the parts of any row don't make a date, the whole query fails with `Date out of range`; no rows are returned. Below, `make_date(year(d), 2, 29)` fails because 2026 isn't a leap year. Check the parts first, or keep only valid rows in `WHERE`.
- **Years outside 1 to 9999 can't be read as Python dates.** The Query Engine builds them, but `datetime.date` in your client holds only years 1 to 9999. Cast such a date to `VARCHAR` to read it.
- **The day number is an `INTEGER`.** A `BIGINT` day number fails with `No function matches the given name and argument types &apos;make_date(BIGINT)&apos;`; cast it: `make_date(CAST(days AS INTEGER))`.
- **Whole numbers only.** Decimal parts fail with `No function matches the given name and argument types`.

```
run(f"SELECT id, make_date(year(d), 2, 29) FROM {TABLE} ORDER BY id LIMIT 10")
run("SELECT make_date(CAST(CAST(20000 AS BIGINT) AS INTEGER))")
```

```
OSError: Date out of range: 2026-2-29
[(datetime.date(2024, 10, 4),)]
```

## Similar functions

| Function | Returns | Result for 29 February 2024 |
| --- | --- | --- |
| `make_date(2024, 2, 29)` | A `DATE` | 2024-02-29 |
| `DATE &apos;2024-02-29&apos;`, `CAST(&apos;2024-02-29&apos; AS DATE)` | A `DATE`, from text | 2024-02-29 |
| [strptime](/develop/docs/dev-database-reference-sql-functions-date-time-strptime) with `&apos;%Y-%m-%d&apos;` | A `TIMESTAMP`, from text in any format | 2024-02-29 00:00:00 |
| [make_timestamp](/develop/docs/dev-database-reference-sql-functions-date-time-make-timestamp) | A `TIMESTAMP`, from parts | 2024-02-29 00:00:00 |
| [date_trunc](/develop/docs/dev-database-reference-sql-functions-date-time-date-trunc) with `&apos;month&apos;` | The first day of the month, as a `DATE` | 2024-02-01 |

```
run("SELECT make_date(2024, 2, 29), DATE '2024-02-29', CAST('2024-02-29' AS DATE), "
    "strptime('2024-02-29', '%Y-%m-%d'), make_timestamp(2024, 2, 29, 0, 0, 0), date_trunc('month', DATE '2024-02-29')")
```

```
[(datetime.date(2024, 2, 29), datetime.date(2024, 2, 29), datetime.date(2024, 2, 29), datetime.datetime(2024, 2, 29, 0, 0), datetime.datetime(2024, 2, 29, 0, 0), datetime.date(2024, 2, 1))]
```

Clean up:

```
run(f"DROP TABLE IF EXISTS {TABLE}")
run(f"DROP SCHEMA IF EXISTS {SCHEMA}")
conn.close()
```

```
[]
[]
```

## See also

- [make_time](/develop/docs/dev-database-reference-sql-functions-date-time-make-time), [make_timestamp](/develop/docs/dev-database-reference-sql-functions-date-time-make-timestamp) and [make_timestamp_ns](/develop/docs/dev-database-reference-sql-functions-date-time-make-timestamp-ns): build a time or a timestamp.
- [Date and time functions](/develop/docs/dev-database-reference-sql-functions-date-time): all the date and time functions.
- [Where filters run](/develop/docs/dev-database-reference-sql-select#where-filters-run) and [pushdowns](/develop/docs/dev-database-reference-pushdowns).
- [EXPLAIN](/develop/docs/dev-database-reference-sql-explain): how to read a plan.