Convert integer date to datetime

Question

Post reply

Convert integer date to datetime

mattias.lundqvist

Old Hand

Points: 327
More actions
March 5, 2014 at 4:01 am

#289111

Hi,
I have 2 columns with integer values, Column1=Date, Column2=Time
and I want to convert them into a datetime format Column3.
How do I convert this integers to datetime?
best regards
//Mattias

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLCJ SSCommitted Points: 1781 More actions · Answer 1

For date conversion you may use

SELECT convert(date,CONVERT(varchar(10),columname,101))

For time please take a look at http://stackoverflow.com/questions/12568408/how-to-convert-an-integer-time-to-hhmmss00-in-sql-server-2008

Experts, is there any other easy way to convert integer to time ?

Ed Wagner SSC Guru Points: 287024 More actions · Answer 2

It depends on what the integers represent.

If, for example, the date integer is in the format YYYYMMDD, a conversion can get you where you want to go.

select CONVERT(datetime, convert(varchar(10), 20120103));

If, on the other hand, the integer is the number of days since 01/01/1900, then simple addition will do the trick. Note I picked this date because date 0 is 01/01/1900 and that I'm using the 14 as your integer value.

select DATEADD(DAY, 14, 0);

Lynn Pettis has a list of common date functions posted at http://www.sqlservercentral.com/blogs/lynnpettis/2009/03/25/some-common-date-routines/. They may help you along the way.

Tally Tables - Performance Personified
String Splitting with True Performance
Best practices on how to ask questions

HanShi SSC-Dedicated Points: 33506 More actions · Answer 3

Like the previous poster (Ed Wagner) allready mentioned, it depends on how your integer values represent a particular date or time.

Can you post some sample values and include the date/time they represent, so we can give you adequate advise?

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

mattias.lundqvist Old Hand Points: 327 More actions · Answer 4

Hi

this how the columns look like and datetime should look like Column3

(Column3) Column1 Column2

2014-02-28 16:13:00.0002014022842857

thanks

//Mattias

HanShi SSC-Dedicated Points: 33506 More actions · Answer 5

mattias.lundqvist (3/5/2014)
Hi
this how the columns look like and datetime should look like Column3
(Column3) Column1 Column2
2014-02-28 16:13:00.0002014022842857
thanks
//Mattias

Can you explain how the value of 42857 represent the time 16:13:00?

If I take this as number of seconds I will end up at 11:54:17.000 and if I take this number as minutes I will end up approx. the next month at 18:17:00.000.

I have used the code below and maybe that will give you a good starting point for your final solution.

create table #test (date_as_int int, time_as_int int)

insert into #test

values(20140228, 42857)

select

date_as_int

, time_as_int

, convert(datetime, convert(char(8), date_as_int)) as date_as_type

, dateadd(second, time_as_int, convert(datetime, convert(char(8), date_as_int))) as datetime_added_seconds

, dateadd(minute, time_as_int, convert(datetime, convert(char(8), date_as_int))) as datetime_added_minutes

from #test

drop table #test

Result:

date_as_inttime_as_intdate_as_typedatetime_added_secondsdatetime_added_minutes

20140228428572014-02-28 00:00:00.0002014-02-28 11:54:17.0002014-03-29 18:17:00.000

** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **