Log in  ::  Register  ::  Not logged in

 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 9:29 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, September 30, 2014 5:22 AM Points: 300, Visits: 1,070
 Hi,How to convert bigint time to datetime (CST)Declare @MyBigIntTime BIGINT =1397750400000Thanks,PSB
Post #1562717
 Posted Thursday, April 17, 2014 9:43 AM
 Old Hand Group: General Forum Members Last Login: Sunday, October 12, 2014 4:00 PM Points: 341, Visits: 751
 What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?
Post #1562734
 Posted Thursday, April 17, 2014 9:49 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, September 30, 2014 5:22 AM Points: 300, Visits: 1,070
 Minutes
Post #1562738
 Posted Thursday, April 17, 2014 10:01 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 2:01 PM Points: 20,861, Visits: 32,889
 Also, what date and time does the value provided equate?
Post #1562742
 Posted Thursday, April 17, 2014 10:32 AM
 SSChampion Group: General Forum Members Last Login: Thursday, December 18, 2014 8:26 AM Points: 10,381, Visits: 13,436
 What is the base date? 1900-01-01, 1970-01-01. The basic idea is:DATEADD(Minute, Integer, BaseDate) Jack CorbettApplications Developer Don't let the good be the enemy of the best. -- Paul FlemingCheck 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 #1562761
 Posted Thursday, April 17, 2014 10:35 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 2:01 PM Points: 20,861, Visits: 32,889
 Unfortunately, you get an overflow error when trying to convert the BIGINT value provided to an INT value when using DATEADD.
Post #1562765
 Posted Thursday, April 17, 2014 10:43 AM
 Old Hand Group: General Forum Members Last Login: Sunday, October 12, 2014 4:00 PM Points: 341, Visits: 751
 Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.
Post #1562773
 Posted Thursday, April 17, 2014 10:47 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 2:01 PM Points: 20,861, Visits: 32,889
 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.
Post #1562775
 Posted Thursday, April 17, 2014 10:48 AM
 Old Hand Group: General Forum Members Last Login: Tuesday, September 30, 2014 5:22 AM Points: 300, Visits: 1,070
 select DATEADD(Minute, 1397750400000, '1970-00-01')Getting an overflow error .Arithmetic overflow error converting expression to data type int.
Post #1562776
 Posted Thursday, April 17, 2014 10:49 AM
 SSC-Insane Group: General Forum Members Last Login: Today @ 2:01 PM Points: 20,861, Visits: 32,889
 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??
Post #1562777

 Permissions