How to create a Date in SQL Server given the Day, Month and Year as Integers

Question

FOR Example if I have:

DECLARE @Day int = 25 DECLARE @Month int  = 10  DECLARE @Year int = 2016 

I want to return

2016-10-25 

As Date or datetime

Answer 1

In SQL Server 2012+, you can use datefromparts():

select datefromparts(@year, @month, @day) 

In earlier versions, you can cast a string. Here is one method:

select cast(cast(@year*10000 + @month*100 + @day as varchar(255)) as date) 

Answer 2

In SQL Server 2012+, you can use DATEFROMPARTS():

DECLARE @Year int = 2016, @Month int = 10, @Day int = 25; SELECT DATEFROMPARTS (@Year, @Month, @Day); 

In earlier versions, one method is to create and convert a string.

There are a few string date formats which SQL Server reliably interprets regardless of the date, language, or internationalization settings.

A six- or eight-digit string is always interpreted as ymd. The month and day must always be two digits.

https://docs.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql

So a string in the format 'yyyymmdd' will always be properly interpreted.

(ISO 8601-- YYYY-MM-DDThh:mm:ss-- also works, but you have to specify time and therefore it's more complicated than you need.)

While you can simply CAST this string as a date, you must use CONVERT in order to specify a style, and you must specify a style in order to be deterministic (if that matters to you).

The "yyyymmdd" format is style 112, so your conversion looks like this:

DECLARE @Year int = 2016, @Month int = 10, @Day int = 25; SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112); 

And it results in:

2016-10-25

Technically, the ISO/112/yyyymmdd format works even with other styles specified. For example, using that text format with style 104 (German, dd.mm.yyyy):

DECLARE @Year int = 2016, @Month int = 10, @Day int = 25; SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),104); 

Also still results in:

2016-10-25

Other formats are not as robust. For example this:

SELECT CASE WHEN CONVERT(date,'01-02-1900',110) = CONVERT(date,'01-02-1900',105) THEN 1 ELSE 0 END; 

Results in:

0

As a side note, with this method, beware that nonsense inputs can yield valid but incorrect dates:

DECLARE @Year int = 2016, @Month int = 0, @Day int = 1025; SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112); 

Also yields:

2016-10-25

DATEFROMPARTS protects you from invalid inputs. This:

DECLARE @Year int = 2016, @Month int = 10, @Day int = 32; SELECT DATEFROMPARTS (@Year, @Month, @Day); 

Yields:

Msg 289, Level 16, State 1, Line 2 Cannot construct data type date, some of the arguments have values which are not valid.

Also beware that this method does not work for dates prior to 1000-01-01. For example:

DECLARE @Year int = 900, @Month int = 1, @Day int = 1; SELECT CONVERT(date,CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),112); 

Yields:

Msg 241, Level 16, State 1, Line 2 Conversion failed when converting date and/or time from character string.

That's because the resulting string, '9000101', is not in the 'yyyymmdd' format. To ensure proper formatting, you'd have to pad it with leading zeroes, at the sacrifice of some small amount of performance. For example:

DECLARE @Year int = 900, @Month int = 1, @Day int = 1; SELECT CONVERT(date,RIGHT('000' + CONVERT(varchar(50),(@Year*10000 + @Month*100 + @Day)),8),112); 

Results in:

0900-01-01

There are other methods aside from string conversion. Several are provided in answers to "Create a date with T-SQL". A notable example involves creating the date by adding years, months, and days to the "zero date".

(This answer was inspired by Gordon Linoff's answer, which I expanded on and provided additional documentation and notes.)