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

 Convertion of integer to Datetime format Rate Topic Display Mode Topic Options
Author
 Message
 Posted Tuesday, August 18, 2009 2:33 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 18, 2009 1:44 AM Points: 5, Visits: 12
 Hello All One of the column in my database contains createdate which gets stored in integer format just like that 1231151827 i want to get it converted into datetime format
Post #772480
 Posted Tuesday, August 18, 2009 2:50 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 Hi,Are you sure this int column having 10 digit characters?
Post #772490
 Posted Tuesday, August 18, 2009 2:55 AM
 SSCommitted Group: General Forum Members Last Login: Friday, April 1, 2016 1:37 AM Points: 1,778, Visits: 8,370
 At a guess , that is the number of seconds from 01jan1970try this`select (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')`
Post #772496
 Posted Tuesday, August 18, 2009 2:59 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 Hi,try this`select convert(datetime,40010/*your int value*/,112/*your format*/)`
Post #772499
 Posted Tuesday, August 18, 2009 3:04 AM
 Ten Centuries Group: General Forum Members Last Login: Monday, July 25, 2016 5:45 AM Points: 1,229, Visits: 3,493
 DATETIME to INT`declare @time1 datetimeset @time1 = getdate()select convert(int,@time1)print @time1`INT to DATETIME`declare @time intset @time = 40042select convert(datetime,@time,112)print @time`
Post #772504
 Posted Tuesday, August 18, 2009 4:49 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 18, 2009 1:44 AM Points: 5, Visits: 12
 Thanks a lot it worked thanks to all who supported it got resolved by putting the queryselect (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')
Post #772551
 Posted Tuesday, August 18, 2009 4:50 AM
 Forum Newbie Group: General Forum Members Last Login: Friday, September 18, 2009 1:44 AM Points: 5, Visits: 12
 harishchede (8/18/2009)Thanks a lot it worked thanks to all who supported it got resolved by putting the queryselect (1231151827/60)select 20519197/60select 341986/24select dateadd(dd,14249,'19700101')
Post #772554
 Posted Tuesday, August 18, 2009 6:11 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:07 AM Points: 14,556, Visits: 38,439
 Dave Ballentine showed you step by step how the conversion is going to work; but if you use the final answer, you don't get the timne portion, just the correct date;you lose the modulus of the integer division;you should shortcut and just use the DATEADD function to add the total seconds:`select dateadd(dd,14249,'19700101') --add the offset for the stored date--2009-01-05 00:00:00.000select (1231151827 % 60) --sec to min gain 7 seconds fromselect 20519197 % 60 --min to hour gain 37 minutesselect 341986 % 24 --hour to days gain 10 hoursselect dateadd(second,1231151827,'19700101')--2009-01-05 10:37:07.000` Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #772606
 Posted Wednesday, August 19, 2009 9:14 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, May 31, 2016 10:41 AM Points: 149, Visits: 307
 That is a pretty interesting problem. I actually discovered that I have a similar problem in a db I just acquired so these solutions will come in handy for me as welL! Love them all ... regardless. - Buddha
Post #773637
 Posted Wednesday, August 19, 2009 9:25 AM
 SSChampion Group: General Forum Members Last Login: Today @ 9:07 AM Points: 14,556, Visits: 38,439
 After Dave inferred that it was the # seconds since 01/01/1970, it was pretty easy...but how the heck did he figure out the starting date? Dave had you tripped over the same issue before? is it a common conversion from some other system(ie Oracle or DB2 or something?) Lowell--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Post #773654

 Permissions