CODEWITHSUNDEEP
sql-server
user2361820
user2361820

Reputation: 449

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

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?

Upvotes: 10

Views: 75503

Answers (6)

Jafar Kofahi
Jafar Kofahi

Reputation: 763

Use the Convert function

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

Replace YEAR with your year column, MM with month, and DAY with your day column. and concatenate to formulate your date string

Upvotes: -2

Aaron Bertrand
Aaron Bertrand

Reputation: 280351

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

Upvotes: 8

i-one
i-one

Reputation: 5120

One more, just to fill up list of various approaches:

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

Upvotes: 0

bummi
bummi

Reputation: 27377

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

Upvotes: 6

Brad
Brad

Reputation: 12255

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

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

If they are numeric you'll need to cast each column to varchar before concatenating otherwise you'll end up getting something funky

Upvotes: -1

Lamak
Lamak

Reputation: 70648

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

Upvotes: 20

Related Questions