April 17, 2014 at 9:29 am
Hi,
How to convert bigint time to datetime (CST)
Declare @MyBigIntTime BIGINT =1397750400000
Thanks,
PSB
April 17, 2014 at 9:43 am
What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?
April 17, 2014 at 9:49 am
Minutes
April 17, 2014 at 10:01 am
Also, what date and time does the value provided equate?
April 17, 2014 at 10:32 am
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
April 17, 2014 at 10:35 am
Unfortunately, you get an overflow error when trying to convert the BIGINT value provided to an INT value when using DATEADD.
April 17, 2014 at 10:43 am
Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.
April 17, 2014 at 10:47 am
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.
April 17, 2014 at 10:48 am
select DATEADD(Minute, 1397750400000, '1970-00-01')
Getting an overflow error .
Arithmetic overflow error converting expression to data type int.
April 17, 2014 at 10:49 am
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??
April 17, 2014 at 11:06 am
SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))
worked for me!
April 17, 2014 at 11:09 am
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.
April 17, 2014 at 11:13 am
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?
April 17, 2014 at 11:41 am
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
April 17, 2014 at 12:28 pm
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
Viewing 15 posts - 1 through 15 (of 28 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy