In DuckDB, the make_date() function enables us to create date values from individual year, month, and day components. This function is particularly useful when working with data that stores date components separately or when constructing date values programmatically.

Basic Syntax

The basic syntax of the make_date() function is:

make_date(year, month, day)

Where:

• year is an integer representing the year
• month is an integer representing the month (1-12)
• day is an integer representing the day of the month (1-31)

It’s also possible to pass the values as a struct.

Examples

Simple Date Construction

SELECT make_date(2025, 3, 10) AS the_date;

Result:

+------------+
|  the_date  |
+------------+
| 2025-03-10 |
+------------+

Converting Separate Columns into a Date

If you have a table with separate columns for year, month, and day:

CREATE TABLE events (
    event_id INTEGER,
    event_year INTEGER,
    event_month INTEGER,
    event_day INTEGER,
    event_name VARCHAR
);

INSERT INTO events VALUES 
    (1, 2025, 3, 10, 'Conference'),
    (2, 2025, 4, 15, 'Workshop');

-- Convert to a single date column
SELECT 
    event_id,
    make_date(event_year, event_month, event_day) AS event_date,
    event_name
FROM events;

Result:

+----------+------------+------------+
| event_id | event_date | event_name |
+----------+------------+------------+
| 1        | 2025-03-10 | Conference |
| 2        | 2025-04-15 | Workshop   |
+----------+------------+------------+

Working with Date Arithmetic

The make_date() function can be handy when combined with date arithmetic:

SELECT 
    make_date(2025, 3, 10) + INTERVAL (n) DAY AS date_window
FROM range(0, 7) t(n);

Result:

+---------------------+
|     date_window     |
+---------------------+
| 2025-03-10 00:00:00 |
| 2025-03-11 00:00:00 |
| 2025-03-12 00:00:00 |
| 2025-03-13 00:00:00 |
| 2025-03-14 00:00:00 |
| 2025-03-15 00:00:00 |
| 2025-03-16 00:00:00 |
+---------------------+

This example returns all dates within a 7-day window. The operation actually converted it to a TIMESTAMP value, but we can cast it to a DATE value if required:

SELECT 
    cast(make_date(2025, 3, 10) + INTERVAL (n) DAY as DATE) AS date_window
FROM range(0, 7) t(n);

Result:

+-------------+
| date_window |
+-------------+
| 2025-03-10  |
| 2025-03-11  |
| 2025-03-12  |
| 2025-03-13  |
| 2025-03-14  |
| 2025-03-15  |
| 2025-03-16  |
+-------------+

Passing a Struct

It’s possible to pass the date parts in a struct:

SELECT make_date({"year" : 2035, "month" : 09, "day" : 25}) AS struct_example;

Result:

+----------------+
| struct_example |
+----------------+
| 2035-09-25     |
+----------------+

Error Handling

The make_date() function performs validation on its inputs.

This query will raise an error due to invalid day for February:

SELECT make_date(2025, 2, 30);

Output:

Conversion Error: Date out of range: 2025-2-30

The following query causes an error due to the month being higher than 12:

SELECT make_date(2025, 13, 09);

Output:

Conversion Error: Date out of range: 2025-13-9

Related Functions

DuckDB offers several related functions:

• make_time(): Creates a time value from hour, minute, and second components
• make_timestamp(): Creates a timestamp from date and time components
• date_part(): Extracts components from a date value (the reverse operation)