Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 Convert Bigint time to datetime Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, April 17, 2014 11:06 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 22, 2016 5:50 AM Points: 434, Visits: 1,403
Post #1562786
 Posted Thursday, April 17, 2014 11:09 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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.
Post #1562788
 Posted Thursday, April 17, 2014 11:13 AM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 9:19 AM Points: 364, Visits: 830
 Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That 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?
Post #1562790
 Posted Thursday, April 17, 2014 11:41 AM
 SSChampion Group: General Forum Members Last Login: Yesterday @ 12:32 PM Points: 10,807, Visits: 14,829
 gbritton1 (4/17/2014)Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That 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 CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingAt best you can say that one job may be more secure than another, but total job security is an illusion. -- Rod at workCheck 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 helpNeed an Answer? Actually, No ... You Need a QuestionHow to Post Performance ProblemsCrosstabs and Pivots or How to turn rows into columns Part 1Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1562800
 Posted Thursday, April 17, 2014 12:28 PM
 Old Hand Group: General Forum Members Last Login: Yesterday @ 9:19 AM Points: 364, Visits: 830
 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
Post #1562814
 Posted Sunday, April 20, 2014 4:24 AM
 SSCertifiable Group: General Forum Members Last Login: Today @ 5:38 AM Points: 6,545, Visits: 17,216
 PSB (4/17/2014)Minutes`1397750400000 Minutes 23295840000 Hours 970660000 Days 2657522 Years`The first birthday of Homo habilis?
Post #1563322
 Posted Sunday, April 20, 2014 5:33 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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.
Post #1563325
 Posted Sunday, April 20, 2014 6:33 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1563374
 Posted Sunday, April 20, 2014 6:35 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 3:23 PM Points: 42,036, Visits: 39,415
 Jack Corbett (4/17/2014)gbritton1 (4/17/2014)Something to consider:`select datediff(m, '0001-01-01', '9999-12-31')`returns 119987That 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1563375
 Posted Monday, April 21, 2014 5:58 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 22, 2016 5:50 AM Points: 434, Visits: 1,403
 I have a couple of 14 digit int time in my tableSELECT 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
Post #1563443

 Permissions