Convert Bigint time to datetime

Question

Post reply

Convert Bigint time to datetime

PSB

SSChampion

Points: 13195
More actions
April 17, 2014 at 9:29 am

#396620

Hi,
How to convert bigint time to datetime (CST)
Declare @MyBigIntTime BIGINT =1397750400000
Thanks,
PSB

Viewing 15 posts - 1 through 15 (of 28 total)

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

gbritton1 SSCertifiable Points: 6522 More actions · Answer 1

What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?

PSB SSChampion Points: 13195 More actions · Answer 2

PSB

SSChampion

Points: 13195

April 17, 2014 at 9:49 am

#1706828

Minutes

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

Also, what date and time does the value provided equate?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

What is the base date? 1900-01-01, 1970-01-01. The basic idea is:

DATEADD(Minute, Integer, BaseDate)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

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

Unfortunately, you get an overflow error when trying to convert the BIGINT value provided to an INT value when using DATEADD.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 6

Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.

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

gbritton1 (4/17/2014)
Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.

Okay, using what for a base? The OP still hasn't told us what the value provided represents.

PSB SSChampion Points: 13195 More actions · Answer 8

select DATEADD(Minute, 1397750400000, '1970-00-01')

Getting an overflow error .

Arithmetic overflow error converting expression to data type int.

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

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??

PSB SSChampion Points: 13195 More actions · Answer 10

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

worked for me!

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

PSB (4/17/2014)
SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))
worked for me!

So the BIGINT value was actually milliseconds since midnight 1970-01-01, not minutes. Good to know.

gbritton1 SSCertifiable Points: 6522 More actions · Answer 12

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?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 13

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.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

gbritton1 SSCertifiable Points: 6522 More actions · Answer 14

Sorry, you're quite right! That should be:

declare @maxminutes bigint = datediff(day, '0001-01-01', '9999-12-31')*cast(24*60 as bigint)

select @maxminutes

which yields: 5258963520

Convert Bigint time to datetime

Cookies on SQLServerCentral