Create a date from day month and year with T-SQL

Crafting dates: DATEFROMPARTS in SQL Server 2012+

In SQL Server 2012 and onward, we use DATEFROMPARTS for date creation. It allows us to use either column values or variables:

DECLARE @year INT = 2023, @month INT = 3, @day INT = 15; 
-- These variables are like the One Ring in the Lord of the Rings, holding immense power!
SELECT DATEFROMPARTS(@year, @month, @day) AS ResultingDate 


This function has the bonus feature of checking any invalid dates you try to create, shouting "You shall not pass!" to them.

Date crafting in older SQL Server versions

Absent DATEFROMPARTS, in SQL Server 2005, nested DATEADD functions incrementally build the date, beginning with the base date (1900-01-01):

-- Just like how 1 is the loneliest number, SQL Server starts counting months and days from 1, and years from 1900.
SELECT DATEADD(DAY, @day - 1,
       DATEADD(MONTH, @month - 1,
       DATEADD(YEAR, @year - 1900, CAST('1900-01-01' AS DATETIME)))) AS ResultingDate


Optimal date creation: avoid string casting

Rev up your SQL Server performance by not casting or converting from strings. SQL Server stores dates as day counts dating from 1900-01-01; leveraging this enables efficient integer arithmetic on dates.

Initiate adjustments with years to accurately register leap years; then proceed to adjust months and days.

Alternate date crafting techniques in T-SQL

Arithmetic assembly - a precise sequence matters

Ensure order of additions is followed—add years as months, then actual months, finally days. Like a good symphony, there's a sequence that pleases SQL Server!

-- SQL Server likes getting months before days, otherwise it gets cranky!
SELECT DATEADD(MONTH, ((@year - 1900) * 12) + @month - 1, 
       DATEADD(DAY, @day - 1, CAST('1900-01-01' AS DATETIME))) AS ResultingDate


String concatenation and casting

Though less efficient, combining date variables into a string then casting might be required in some cases:

-- Writing a Y-M-D formatted string is almost as fun as writing a letter to yourself in the past... but with more SQL.
SELECT CAST(CAST(@year AS VARCHAR(4)) + '-' + RIGHT('0' + CAST(@month AS VARCHAR(2)), 2) + '-' + RIGHT('0' + CAST(@day AS VARCHAR(2)), 2) AS DATE) AS ResultingDate


Don't ignore zero-padding for single-digit months and days to maintain Y-M-D formatting!

Common pitfalls and how to avoid them

Validating input

Check your date components with the eye of a detective, ensuring validity and data types of inputs. Enlist TRY_CONVERT or TRY_CAST for proactive error detection:

SELECT TRY_CAST(
         TRY_CAST(@year AS VARCHAR(4)) + '-' + 
         FORMAT(@month, '00') + '-' + 
         FORMAT(@day, '00') AS DATE
       ) AS ResultingDate


Zero-padding for single-digit months and days

Zero-pad single-digit months and days to keep your date strings in Y-M-D format:

SELECT CAST(FORMAT(@year, '0000') + '-' + FORMAT(@month, '00') + '-' + FORMAT(@day, '00') AS DATE) AS ResultingDate


This ensures correct string format for any date construction.