---
title: "How to create a date from year, month, and day components in ClickHouse®"
excerpt: "Learn how to create dates from year, month, and day components in ClickHouse® using makeDate() function with syntax examples, validation rules, and API integration."
authors: "Cameron Archer"
categories: "AI Resources"
createdOn: "2025-09-18 16:20:55"
publishedOn: "2025-09-18 16:20:55"
updatedOn: "2025-09-18 16:20:55"
status: "published"
---

Dates are often stored or received as separate values for year, month, and day. In ClickHouse{% sup %}®{% /sup %}, it is common to transform these separate values into a single Date type for querying, filtering, or storing data.

The ClickHouse{% sup %}®{% /sup %} SQL language includes several functions for working with dates and times. One of the most direct ways to construct a Date value from its components is with the `makeDate` function.

This guide explains, step by step, how to create a Date from year, month, and day values in ClickHouse{% sup %}®{% /sup %}. The examples use the current date, September 18, 2025, to show how the function works.

## The fastest way to build a date from components

The simplest way to create a Date from year, month, and day in ClickHouse{% sup %}®{% /sup %} is to use the `makeDate(year, month, day)` function. This function takes three integer arguments and returns a Date value.

Here's the basic syntax:

```sql
SELECT makeDate(2025, 9, 18) AS event_date;
```

The output shows the constructed date:

```plaintext
┌─event_date─┐
│ 2025-09-18 │
└────────────┘
```

### Running makeDate in a SELECT statement

You can test the `makeDate` function directly in a SELECT statement without any table. This approach lets you see exactly how the function works:

```sql
SELECT
    makeDate(2025, 9, 18) AS today,
    makeDate(2024, 2, 29) AS leap_day,
    makeDate(2023, 12, 31) AS year_end;
```

The function accepts integers for each component and returns properly formatted Date values.

### Inserting dates using makeDate in VALUES

When inserting data into a table, `makeDate` works inside INSERT statements. If you have a table called `events` with a Date column named `event_date`:

```sql
INSERT INTO events (event_date, description)
VALUES (makeDate(2025, 9, 18), 'Product launch');
```

This approach is useful when your source data provides year, month, and day as separate fields rather than as a formatted date string.

## makeDate function syntax and version support

The `makeDate` function comes in two forms. The standard version takes three arguments:

```sql
makeDate(year, month, day)
```

A second version accepts just two arguments:

```sql
makeDate(year, day_of_year)
```

In the two-argument form, `day_of_year` represents the day number within the year (1 to 365, or 366 for leap years).

- **Function availability:** `makeDate` was introduced in ClickHouse{% sup %}®{% /sup %} version 22.6
- **Return type:** The function returns a [Date](https://clickhouse.com/docs/sql-reference/data-types/date) value
- **Supported range:** Date values work from 1970-01-01 to 2149-06-06

Each argument accepts integer, float, or decimal input types. ClickHouse{% sup %}®{% /sup %} converts these to integers internally before constructing the date.

## Parameter rules and validation logic

The `makeDate` function validates all inputs to confirm they form a real calendar date. If any value falls outside acceptable ranges, ClickHouse{% sup %}®{% /sup %} returns an error rather than creating an invalid date.

### Month and day ranges

Month values work from 1 to 12, where 1 represents January and 12 represents December. Day values depend on the specific month and year:

- **January, March, May, July, August, October, December:** 1 to 31 days
- **April, June, September, November:** 1 to 30 days
- **February:** 1 to 28 days (or 29 in leap years)

If you try `makeDate(2025, 13, 1)`, ClickHouse{% sup %}®{% /sup %} will throw an error because month 13 doesn't exist.

### Leap year handling

February 29 only works in leap years. ClickHouse{% sup %}®{% /sup %} follows standard leap year rules:

- Years divisible by 4 are leap years
- Years divisible by 100 are not leap years
- Years divisible by 400 are leap years

So `makeDate(2024, 2, 29)` works because 2024 is a leap year, but `makeDate(2025, 2, 29)` fails because 2025 is not.

## Choosing between makeDate, makeDate32, makeDateTime, and YYYYMMDDToDate

ClickHouse{% sup %}®{% /sup %} provides several functions for creating dates from numeric inputs. Each serves different use cases:

| Function | Input Format | Use Case | Output Type | Date Range |
| -------- | ------------ | -------- | ----------- | ---------- |
| makeDate | year, month, day | Standard date construction | Date | 1970-2149 |
| makeDate32 | year, month, day | Extended date range | Date32 | Much wider range |
| makeDateTime | year, month, day, hour, minute, second | Date with time components | DateTime | 1970-2106 |
| YYYYMMDDToDate | YYYYMMDD integer | Convert 8-digit numbers | Date | 1970-2149 |

### Date range limits

The standard Date type in ClickHouse{% sup %}®{% /sup %} covers dates from 1970-01-01 through 2149-06-06. For dates outside this range, use `makeDate32`, which returns a Date32 value with a much broader range.

```sql
-- This works with makeDate
SELECT makeDate(2100, 1, 1) AS future_date;

-- For dates beyond 2149, use makeDate32
SELECT makeDate32(2200, 1, 1) AS far_future_date;
```

### Time zone requirements

The `makeDate` and `makeDate32` functions create date-only values without time or timezone information. For datetime values that include time components and timezone handling, use `makeDateTime`:

```sql
SELECT makeDateTime(2025, 9, 18, 14, 30, 0, 'America/New_York') AS datetime_with_tz;
```

## Converting string or integer formats to date

Many datasets store dates as strings or integers rather than separate components. ClickHouse{% sup %}®{% /sup %} provides multiple conversion options for these formats.

### ClickHouse{% sup %}®{% /sup %} string to datetime patterns

For converting strings to dates, ClickHouse{% sup %}®{% /sup %} offers several parsing functions:

- **toDate():** Converts ISO format strings like '2025-09-18'
- **parseDateTime():** Handles custom format patterns
- **parseDateTimeBestEffort():** Attempts to parse various string formats automatically

```sql
-- Simple string conversion
SELECT toDate('2025-09-18') AS simple_conversion;

-- Custom format parsing
SELECT parseDateTime('18/09/2025 14:30:00', '%d/%m/%Y %H:%i:%s') AS custom_format;

-- Best effort parsing
SELECT parseDateTimeBestEffort('September 18, 2025') AS flexible_parsing;
```

The `toDateTime` function works similarly for strings that include time components.

### YYYYMMDDToDate usage

When dates are stored as 8-digit integers (like 20250918 for September 18, 2025), use `YYYYMMDDToDate`:

```sql
SELECT YYYYMMDDToDate(20250918) AS converted_date;
```

This function is particularly useful when working with data exports from systems that format dates as integers. For extended date ranges, `YYYYMMDDToDate32` provides the same functionality with Date32 output.

## Bulk column conversion in existing tables

Real-world scenarios often involve tables with separate year, month, and day columns that need combining into a single Date column. ClickHouse{% sup %}®{% /sup %} handles this through table updates or materialized views.

### ALTER TABLE UPDATE pattern

When a table already contains separate date component columns, use `ALTER TABLE ... UPDATE` to populate a new Date column:

```sql
-- Add a new Date column to the existing table
ALTER TABLE events ADD COLUMN event_date Date;

-- Populate the new column using existing year, month, day columns
ALTER TABLE events
UPDATE event_date = makeDate(year, month, day)
WHERE event_date IS NULL;
```

This approach works well for one-time conversions of existing data.

### Creating a materialized view

For ongoing data ingestion where new rows arrive with separate date components, a [materialized view](https://www.tinybird.co/docs/forward/work-with-data/optimize/materialized-views) automatically handles the conversion. For a comprehensive guide on [what materialized views are and why they matter for real-time analytics](https://www.tinybird.co/blog-posts/what-are-materialized-views-and-why-do-they-matter-for-real-time), this approach can significantly improve performance for date construction workflows:

```sql
CREATE MATERIALIZED VIEW events_with_dates
ENGINE = MergeTree()
ORDER BY event_date
AS
SELECT
    makeDate(year, month, day) AS event_date,
    event_type,
    value
FROM raw_events;
```

This view processes each new row from `raw_events`, constructs the date, and stores it alongside other columns.

## Building a time series analytics API with Tinybird

[Tinybird](https://www.tinybird.co) excels at time series analytics by combining ClickHouse{% sup %}®{% /sup %}'s columnar performance and aggregation capabilities with real-time API generation and hosting. Here's how to build an analytics API endpoint that constructs dates from components and aggregates user engagement metrics over time.

### Create the time series data source

Start with event analytics data containing separate date components. For example, you could create an NDJSON file named `user_events.ndjson`:

```json
{"user_id": "u1", "event_type": "page_view", "year": 2025, "month": 9, "day": 15, "hour": 14, "duration_seconds": 45}
{"user_id": "u2", "event_type": "conversion", "year": 2025, "month": 9, "day": 15, "hour": 14, "duration_seconds": 120}
{"user_id": "u1", "event_type": "page_view", "year": 2025, "month": 9, "day": 16, "hour": 9, "duration_seconds": 30}
{"user_id": "u3", "event_type": "page_view", "year": 2025, "month": 9, "day": 16, "hour": 10, "duration_seconds": 75}
{"user_id": "u2", "event_type": "conversion", "year": 2025, "month": 9, "day": 16, "hour": 11, "duration_seconds": 90}
```

Upload this as a Tinybird [data source](https://www.tinybird.co/docs/forward/get-data-in/data-sources):

```bash
tb datasource create --file user_events.ndjson --name user_events
tb --cloud deploy
tb datasource append user_events user_events.ndjson
```

### Build the aggregation pipe

Create a [pipe](https://www.tinybird.co/docs/forward/work-with-data/pipes) that constructs dates and aggregates metrics by time periods:

```tinybird
NODE daily_metrics
SQL >
    %
    WITH constructed_dates AS (
        SELECT
            user_id,
            event_type,
            makeDate(year, month, day) AS event_date,
            makeDateTime(year, month, day, hour, 0, 0) AS event_datetime,
            duration_seconds
        FROM user_events
        WHERE makeDate(year, month, day) BETWEEN
            {{Date(start_date, '2025-09-01')}} AND
            {{Date(end_date, '2025-09-30')}}
    )
    SELECT
        event_date,
        COUNT(*) AS total_events,
        uniq(user_id) AS unique_users,
        countIf(event_type = 'conversion') AS conversions,
        countIf(event_type = 'page_view') AS page_views,
        round(countIf(event_type = 'conversion') / uniq(user_id) * 100, 2) AS conversion_rate_percent,
        round(avg(duration_seconds), 1) AS avg_duration_seconds,
        min(event_datetime) AS first_event_time,
        max(event_datetime) AS last_event_time
    FROM constructed_dates
    GROUP BY event_date
    ORDER BY event_date
    {\% if defined(limit) %}
    LIMIT {{Int32(limit, 30)}}
    {\% end %}

TYPE ENDPOINT
```

This query demonstrates ClickHouse{% sup %}®{% /sup %}'s time series strengths:

- **Date construction** from separate year/month/day fields using `makeDate()`
- **Time aggregation** with `GROUP BY` on constructed dates
- **Unique counting** with `uniq()` for distinct users
- **Conditional aggregation** with `countIf()` for event types
- **Statistical functions** like `avg()`, `min()`, `max()`
- **Dynamic date ranges** with parameterized start/end dates

For more advanced [time series generation techniques](https://www.tinybird.co/blog-posts/tips-8-generating-time-series-on-clickhouse), ClickHouse{% sup %}®{% /sup %} provides powerful functions for creating complete time series data from scratch.

### Deploy and query the analytics API

Deploy the pipe:

```bash
tb --cloud deploy
```

Query daily metrics for a date range:

```bash
curl "https://api.tinybird.co/v0/pipes/daily_metrics.json?start_date=2025-09-15&end_date=2025-09-16&limit=10"
```

The response provides aggregated time series analytics:

```json
{
  "data": [
    {
      "event_date": "2025-09-15",
      "total_events": 2,
      "unique_users": 2,
      "conversions": 1,
      "page_views": 1,
      "conversion_rate_percent": 50.0,
      "avg_duration_seconds": 82.5,
      "first_event_time": "2025-09-15 14:00:00",
      "last_event_time": "2025-09-15 14:00:00"
    },
    {
      "event_date": "2025-09-16",
      "total_events": 3,
      "unique_users": 3,
      "conversions": 1,
      "page_views": 2,
      "conversion_rate_percent": 33.33,
      "avg_duration_seconds": 65.0,
      "first_event_time": "2025-09-16 09:00:00",
      "last_event_time": "2025-09-16 11:00:00"
    }
  ]
}
```

This approach showcases how ClickHouse{% sup %}®{% /sup %}'s date construction functions enable powerful time series analytics, transforming raw event components into actionable business metrics.

## Next steps with Tinybird for hassle-free ClickHouse{% sup %}®{% /sup %}

Working with dates and times becomes much easier when you don't have to manage the underlying ClickHouse{% sup %}®{% /sup %} infrastructure yourself. [Managed platforms](https://www.tinybird.co/blog-posts/managed-clickhouse-options) like Tinybird handle the technical complexity while providing tools for data ingestion, schema evolution, and API deployment.

Tinybird supports SQL-based development with version control and observability, with zero need to handle infrastructure maintenance, scaling, or DevOps.

A free plan is available for exploring the platform and building proof-of-concept applications. [Sign up for free](https://cloud.tinybird.co/signup)

## FAQs about ClickHouse{% sup %}®{% /sup %} date construction

### Can makeDate accept string arguments instead of integers?

ClickHouse{% sup %}®{% /sup %} automatically converts string representations of numbers to integers for the `makeDate` function. Using `makeDate('2025', '9', '18')` works the same as `makeDate(2025, 9, 18)`.

### What happens when makeDate receives invalid date components?

ClickHouse{% sup %}®{% /sup %} throws an error if the provided values don't form a valid calendar date. For example, `makeDate(2025, 2, 30)` fails because February never has 30 days.

### Does makeDate behavior change across different time zones?

The `makeDate` function produces identical results regardless of server timezone settings. Date values in ClickHouse{% sup %}®{% /sup %} represent calendar days without any time or timezone context.

### Can I use makeDate with NULL values in the arguments?

If any argument to `makeDate` is NULL, the function returns NULL rather than throwing an error. This behavior allows the function to work safely with datasets that contain missing date components.