MAKE_DATE

Construct a DATE from integer year, month, and day components.

Category: datetimeReturns: DATEDialect: Standard

Syntax

MAKE_DATE(year, month, day)

Description

## Overview Constructs a DATE value from three separate INTEGER components. Useful when year, month, and day are available as separate columns (for example in a fiscal-calendar dimension table) and you need to assemble them into a proper DATE. ## Behavior - Returns a DATE. - Returns NULL if any argument is NULL. - Raises an error for invalid dates: month outside 1-12, day outside 1-28/29/30/31 for the given month, or impossible combinations like February 30. - Correctly handles leap years: MAKE_DATE(2024, 2, 29) succeeds, MAKE_DATE(2025, 2, 29) raises an error. - Does not perform end-of-month clamping; use LEAST(day, DAYOFMONTH(LAST_DAY(...))) patterns if you want graceful handling of out-of-range days. ## Compatibility - Matches the MAKE_DATE convention used across analytical SQL engines. An alternative is casting a composed string (yyyy-MM-dd) with TO_DATE, though MAKE_DATE avoids the string-formatting round trip.

Parameters

Name	Type	Description
`year`		Specifies the year component as an INTEGER (for example 2025). Negative values are accepted for BCE dates in engines that support them.
`month`		Specifies the month component as an INTEGER in the range 1 through 12.
`day`		Specifies the day-of-month as an INTEGER in the valid range for the given month (1 through 28, 29, 30, or 31 depending on month and leap year).

Examples

-- Build a specific date
SELECT MAKE_DATE(2025, 3, 15) AS d;

-- New Year's Day
SELECT MAKE_DATE(2025, 1, 1) AS d;

-- Leap day
SELECT MAKE_DATE(2024, 2, 29) AS leap_day;

-- Build a period-start date from a fiscal-calendar table
SELECT MAKE_DATE(fiscal_year, fiscal_month, 1) AS period_start
FROM finance.accounting.fiscal_periods;

-- Error case: February 30 is invalid
SELECT MAKE_DATE(2025, 2, 30); -- raises an error

-- Build historic anchor dates for cohort analysis
SELECT cohort_id, MAKE_DATE(cohort_year, 1, 1) AS cohort_start
FROM analytics.cohorts.definitions;

Pitfalls

MAKE_DATE raises an error for invalid inputs rather than returning NULL or clamping. Validate source data (especially day-of-month for February and 30-day months) before calling, or wrap in TRY.
Years outside the supported DATE range raise errors. Very old historical dates (before 0001) or very far future dates (beyond 9999) are not representable.
There is no end-of-month clamping. To build 'last day of February in year Y' safely, use LAST_DAY(MAKE_DATE(Y, 2, 1)) rather than MAKE_DATE(Y, 2, 29).