Convert Bigint time to datetime

Question

Post reply

Convert Bigint time to datetime

Viewing 13 posts - 16 through 27 (of 27 total)

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

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 1

PSB (4/17/2014)
Minutes

1397750400000 Minutes

23295840000 Hours

970660000 Days

2657522 Years

The first birthday of Homo habilis?

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

Eirikur Eiriksson (4/20/2014)
PSB (4/17/2014)
Minutes
1397750400000 Minutes
23295840000 Hours
970660000 Days
2657522 Years
The first birthday of Homo habilis?
😎

Actually, it was milliseconds. 😉

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

gbritton1 (4/17/2014)
Just do it in a loop, ...

I hope your not serious. Let's see the code that does it in a loop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

Jack Corbett (4/17/2014)
gbritton1 (4/17/2014)
Something to consider:
select datediff(m, '0001-01-01', '9999-12-31')
returns 119987
That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.
Is 1397750400000 the minute of the Big Bang or something like that?
the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation.

Heh... oddly enough, that's why I use the 2 character abbreviations for dateparts.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

PSB SSChampion Points: 13195 More actions · Answer 5

I have a couple of 14 digit int time in my table

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))

The above query throws an error :

Arithmetic overflow error converting expression to data type int.

Please advise how di I resolve it.

Thanks,

PSB

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 6

PSB (4/21/2014)
I have a couple of 14 digit int time in my table
SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))
The above query throws an error :
Arithmetic overflow error converting expression to data type int.
Please advise how di I resolve it.
Thanks,
PSB

Look at the numbers you are working with,

INT Max Value => (2^31 - 1) = 2147483647

61353491400000 / 1000 = 61353491400

almost 30 times the maximum integer value, suggest you change your code to

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000000, convert(datetime, '1-1-1970 00:00:00')))

and work from that.

😎

PSB SSChampion Points: 13195 More actions · Answer 7

According to the query time returned is 1970-03-12 19:15:49.000 .

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 6135349140000) / 1000000, convert(datetime, '1-1-1970 00:00:00')))

The correct time that should be returned is 2014 -03-20 03:27:00.00

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 8

PSB (4/21/2014)
According to the query time returned is 1970-03-12 19:15:49.000 .
SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 6135349140000) / 1000000, convert(datetime, '1-1-1970 00:00:00')))
The correct time that should be returned is 2014 -03-20 03:27:00.00

That is interesting, especially since earlier you said the 1397750400000 was equivalent to 2014-04-17 11:00:00.00. Interesting that a larger value should represent an earlier time.

I guess we need more information if we are going to help you figure this out. Does the application that reads this data output the date/time in a readable format? Perhaps if you show us a couple of rows of data as displayed in the application along with the corresponding raw data we could help figure this out.

Eirikur Eiriksson SSC Guru Points: 182916 More actions · Answer 9

PSB (4/21/2014)
According to the query time returned is 1970-03-12 19:15:49.000 .
SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 6135349140000) / 1000000, convert(datetime, '1-1-1970 00:00:00')))
The correct time that should be returned is 2014 -03-20 03:27:00.00

Question: where does the reference date of 1-1-1970 come from, are you working with unix dates:

Secondly, consider these numbers for an average year (365.25 days)

8766Hours

525960Minutes

31557600Seconds

😎

Federico Iori SSCommitted Points: 1625 More actions · Answer 10

Lynn Pettis (4/17/2014)
PSB (4/17/2014)
select DATEADD(Minute, 1397750400000, '1970-00-01')
Getting an overflow error .
Arithmetic overflow error converting expression to data type int.
Yep, told you that would happen earlier.
Now, what date and time does 1397750400000 represent??

Give a look to http://www.sqlservercentral.com/scripts/TIMESTAMP/76877/

By the way, I believe they are seconds, not minutes

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 11

If it turns out that the time is stored in milliseconds with a base of 1970-01-01 00:00:00.000, then this code will work.

-- Milliseconds since 1970-01-01 00:00:00.000

declare @TimeMS bigint = 1397750400000;

select

[Date/Time] =

-- Verify time can be converted to datetime

case when @TimeMS between -6847804800001 and 253402300799998

then dateadd(ms,@TimeMS%86400000,(@TimeMS/86400000)+25567)

else null end

Results:

Date/Time

-----------------------

2014-04-17 16:00:00.000

This will do the reverse conversion:

declare @DateTime datetime = '2014-04-17 16:00:00.000'

select

TimeMS =

(datediff(dd,25567,@DateTime)*00000086400000)+

datediff(ms,dateadd(dd,datediff(dd,0,@DateTime),0),@DateTime)

Results:

TimeMS

-------------------

1397750400000

Note:

I just realized this is an old post, but the answer is correct if anyone cares.

It's just a bit of code I had laying around from an old project. 😎

PSB SSChampion Points: 13195 More actions · Answer 12

Hi,

Some of the values are also 14 digit . If I use your code below , it returns incorrect dates as shown below.

declare @TimeMS bigint = 61353491400000;

select

[Date/Time] =

-- Verify time can be converted to datetime

case when @TimeMS between -6847804800001 and 253402300799998

then dateadd(ms,@TimeMS%86400000,(@TimeMS/86400000)+25567)

else null end

--INCORRECT RESULT

Date/Time

3914-03-20 20:30:00.000

webrunner SSC-Dedicated Points: 31885 More actions · Answer 13

Way late to the party, I know. But I just ran into this question while checking some data in the SQL Monitor repository. This page proved helpful:

https://lazarus987.wordpress.com/2014/07/03/how-to-convert-tick-to-datetime-sql/

Declare @TickValue bigint Declare @Days float

Set @TickValue = 635399730000000000 -- ENTER TICKS Select @Days = @TickValue * POWER(10.00000000000,-7) / 60 / 60 / 24

Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE)), Cast( (@Days - FLOOR(@Days)) As DateTime)

-------------------
A SQL query walks into a bar and sees two tables. He walks up to them and asks, "Can I join you?"
Ref.: http://tkyte.blogspot.com/2009/02/sql-joke.html