Convert number to datetime SQL Server

SQL Server > Built-in Functions > CAST > Numbers to Date

Sometimes we need to convert numbers (year, month, day) to date like DateSerial.

In order to be independent of the language and locale settings, you should use the ISO 8601 YYYYMMDD format - this will work on any SQL Server system with any language and regional setting in effect.

Example:

declare @year int

declare @month int
declare @day int

set @year = 2012

set @month = 9
set @day = 30

SELECT

CAST(
CAST(@year AS VARCHAR(4)) +
RIGHT('0' + CAST(@month AS VARCHAR(2)), 2) +
RIGHT('0' + CAST(@day AS VARCHAR(2)), 2)
AS DATETIME)

Result:

2012-09-30 00:00:00.000