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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 22, 2016 5:50 AM Points: 434, Visits: 1,403
 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: Yesterday @ 9:19 AM Points: 364, Visits: 830
 What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?
Post #1562734
 Posted Thursday, April 17, 2014 9:49 AM
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 22, 2016 5:50 AM Points: 434, Visits: 1,403
 Minutes
Post #1562738
 Posted Thursday, April 17, 2014 10:01 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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: Yesterday @ 12:32 PM Points: 10,807, Visits: 14,829
 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 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 #1562761
 Posted Thursday, April 17, 2014 10:35 AM
 SSC-Insane Group: General Forum Members Last Login: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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: Yesterday @ 9:19 AM Points: 364, Visits: 830
 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: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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
 SSC-Addicted Group: General Forum Members Last Login: Thursday, September 22, 2016 5:50 AM Points: 434, Visits: 1,403
 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: Yesterday @ 4:50 PM Points: 23,515, Visits: 37,731
 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