How to turn separate year, month and day columns into a single date?

Question

I have a year column that contains things like 2013, 2012, etc. A month column that displays 1-12, and a day column that contains 1-31. I need to run a select that concatenates them and casts them as an actual date, but I am unsure how to go about this. Can anyone provide some input?

Answer 1

In SQL Server 2012, you will probably be better off avoiding string concatenation or complicated math, as they created a function seemingly just for you:

SELECT DATEFROMPARTS(2013, 8, 19);

Of course, storing the data wrong in the first place can lead to problems - for example, what constraint prevents y = 2013, m = 2 and d = 31 from being in the table? You'd think you could wrap that with TRY_CONVERT(), but not so much:

SELECT TRY_CONVERT(DATE, DATEFROMPARTS(2013, 2, 31));

Error:

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

So, in order to prevent bad data from getting into these three columns, you will need to use one of the above cumbersome approaches in a check constraint or a trigger...

...or...

...in any version, you could fix the table and store a date (or datetime) in the first place. You get all the benefits of automatic validation as well as intrinsic date/time functionality that you don't get with three separate unrelated integers. Much better off pulling the parts out when you need them separately (with computed columns, a view, or at query time) from a value that is guaranteed to be a date, than try to rely on the individual parts to form a valid date...

Answer 2

For SQL Server 2008+:

SELECT CONVERT(DATE,CAST([Year] AS VARCHAR(4))+'-'+
                    CAST([Month] AS VARCHAR(2))+'-'+
                    CAST([Day] AS VARCHAR(2)))

For SQL Server 2005:

SELECT CONVERT(DATETIME,CAST([Year] AS VARCHAR(4))+
                        RIGHT('00'+CAST([Month] AS VARCHAR(2)),2)+
                        RIGHT('00'+CAST([Day] AS VARCHAR(2)),2))