CODEWITHSUNDEEP
sqlsql-servert-sqldatetime
sensei
sensei

Reputation: 7592

SQL int to string to datetime

I have date in integers.

What is the cleanest way to convert integers to datetime?

I used this messy way:

CONVERT(DATETIME,  cast(source.Day as varchar(2)) + '/' + cast(source.Month as varchar(2)) + '/' + cast(source.Year as varchar(2)), 3),

Upvotes: 1

Views: 512

Answers (4)

Zerotoinfinity
Zerotoinfinity

Reputation: 6540

Here is how you can do that

   Declare @date date, 
        @year INT = 2014,
        @Month INT = 10,
        @DateDay INT = 12,
        @parseDate char(20)

select @date = CONVERT(date, 
                CAST( Cast(@Year as char(4))
                         + Cast(@Month as char(2)) +
                          cast(@DateDay as char(2))  AS CHAR(12)), 112)
select @date

Note: I am not handling NULL cases.

Upvotes: 2

whytheq
whytheq

Reputation: 35605

Working from R.T.s solution this might work

CREATE TABLE DATA(yr int, mth int, dy int);
INSERT INTO DATA
VALUES (2014,10,15);

SELECT
      CONVERT (DATETIME,
                CONVERT(char(4), yr) + 
                CONVERT(char(2), mth) + 
                CONVERT(char(2), dy)
       ) as Result
FROM DATA

This is still a bit messy and assumes a 4 digit year integer.

SQL Fiddle here: http://sqlfiddle.com/#!6/cbf62/2

Upvotes: 1

gotqn
gotqn

Reputation: 43666

You can use DATEFROMPARTS function:

SELECT DATEFROMPARTS ( 2010, 12, 31 ) AS Result;

Note, it is only available in 2012+ editions.

Upvotes: 2

Rahul Tripathi
Rahul Tripathi

Reputation: 172608

Try this:

select CONVERT (datetime,convert(char(8),yourvalue))

Upvotes: 1

Related Questions