Converting a date/time stored as a bigint

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 2005 Forums

Transact-SQL (2005)

Converting a date/time stored as a bigint

Author

Topic

binarymechanic
Starting Member

2 Posts

Posted - 2008-08-15 : 11:42:58

I have a bigint stored in a 3rd party database that I need to report off of.

A sample value is: 1218456040709
which corresponds to: 08/11/08 8:00:40 AM

..but I have no idea how to convert. Some other algorithms I've seen work with a number that is 4 more digits longer, but not this one. Anyone seen this before or have an idea?

Thanks,
Robert

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 11:57:24

To be able to help you, we need a second reference point.
As of now, we can't tell which starting point to use.
Is 1218456040709 number of seconds, or milliseconds since a certain time?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-15 : 11:59:22

SELECT DATEADD(SECOND, 1218456040709 / 1000, '19691231 20:00')

E 12°55'05.25"
N 56°04'39.16"

binarymechanic
Starting Member

2 Posts

Posted - 2008-08-20 : 13:59:14

Thanks, Peso - right on.

The reason why is was 4 hours behind 1/1/1970 is because it also calculates the difference between UTC time and current time (which is -4 for my server).

Thanks so much,
Robert

create function fnBigint2Datetime ( @datetime bigint )
returns datetime
as
begin
declare @dtm datetime
set @dtm = dateadd(hh,-4,dateadd(ss,@datetime/1000,'1970/01/01 00:00'))
return ( @dtm )
end

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-08-20 : 16:47:32

You query will be faster if you make it as an inline function.

create function fnBigint2Datetime ( @datetime bigint )
returns datetime
as
begin
return dateadd(ss, @datetime/1000, '19691231 20:00')
end

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources