Convert int to datetime - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Convert int to datetime

Author

Topic

millepag
Starting Member

2 Posts

Posted - 2006-05-23 : 05:00:27

I have a column in which a datetime is saved in int-format. I got a couple of exampels:

DB-value - Converted to datetimestring
1148369821 = 2006-05-23 08:37
1148373000 = 2006-05-23 09:30
1148374228 = 2006-05-23 08:44

I got an application that converts the int into a datetime-string (the one to the right), but i want to know how to convert it myself so i can use it in my queries.

Anyone know how I can convert the int to a datetime value?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 05:16:51

[code]select dateadd(second, 1148369821, '1970-01-01')[/code]

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-05-23 : 05:21:02

why 1970-01-01 any specific reason??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 05:25:06

There seems to be some descrepancy. Is the time in int format number of seconds since 1970-01-01 ?
There is an hour diff for the first 2 records and 6 mins for the 3rd records

select 	*, dateadd(second, timeint, '1970-01-01') as converted
from
(
	select	1148369821 as timeint, '2006-05-23 08:37' as timestr union all
	select	1148373000 as timeint, '2006-05-23 09:30' as timestr union all
	select	1148374228 as timeint, '2006-05-23 08:44' as timestr
) a

/* RESULT :
timeint     timestr          converted                                              
----------- ---------------- -----------------------
1148369821  2006-05-23 08:37 2006-05-23 07:37:01.000
1148373000  2006-05-23 09:30 2006-05-23 08:30:00.000
1148374228  2006-05-23 08:44 2006-05-23 08:50:28.000
*/

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-05-23 : 05:27:14

quote:
Originally posted by chiragkhabaria

why 1970-01-01 any specific reason??

If Debugging is the process of removing Bugs then i Guess programming should be process of Adding them.

Unix time stores number of seconds since 1970-01-01. http://en.wikipedia.org/wiki/Unix_time

millepag
Starting Member

2 Posts

Posted - 2006-05-23 : 06:35:42

Thank you very much khtan for your fast and good answer.
It works perfectly, I think I just copy/pasted the wrong "converted datetimestring" on the one with 6 minutes diff, cause it seem to work fine now!

There is an hour diff still though, but I think that has to do with the system time on the server that inserts the values. So I'm happy.

Thanks again!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources