How to include separate columns of the year, month and day on the same date?

Question

I have a year column that contains things like 2013, 2012, etc. The column of the month that displays 1-12, and the daily column containing 1-31. I need to run select, which concatenates them and displays them as the actual date, but I'm not sure how to do this. Can someone provide any input?

Answer 1

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)) 

Answer 2

 SELECT CAST(STR(10000 * Year + 100 * Month + Day) AS DATETIME) 

Answer 3

In SQL Server 2012, you probably would be better off avoiding string concatenation or complex math, as they created a function that seems to be just for you:

 SELECT DATEFROMPARTS(2013, 8, 19); 

Of course, improper storage of data in the first place can lead to problems - for example, what restriction prevents y = 2013, m = 2 and d = 31 from the table? You could wrap this with TRY_CONVERT() , but not so much:

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

Error:

Msg 289, Level 16, State 1, Line 3
It is not possible to create a data type date; some of the arguments have invalid values.

So, to prevent bad data from falling into these three columns, you will need to use one of the aforementioned cumbersome approaches in a control constraint or trigger ...

... or...

... in any version, you can fix the table and save the date (or date and time) in the first place. You get all the benefits of automatic validation, as well as built-in date and time functions that you don't get with three separate unrelated integers. It is much better to pull out parts when you need them individually (with calculated columns, a view, or during a query) from a value that is guaranteed to be a date, than trying to rely on individual parts to form a valid date ...

Answer 4

Another one, just to populate a list of different approaches:

 select dateadd(d, td-1, dateadd(m, tm-1, dateadd(yy, ty-1900, 0))) from (values (2011, 10, 26) ,(2012, 1, 5) ,(2013, 7, 15) ) t(y, m, d) 

Answer 5

You can try something like this if your columns are character columns

 Select Cast([year] + '-' + [month] + '-' + [day] as datetime) From yourTable 

If they are numeric, you need to drop each column in varchar before concatenation, otherwise you will end up with something funky

Answer 6

Use the Convert function

 Select Convert(datetime ,YEAR + '/' + MM + '/' + DAY) 

Replace YEAR year column, MM for the month, and DAY for the day column. and concatenation to formulate a date string