|
|
|
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
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231,
Visits: 3,483
|
|
Hi,
Are you sure this int column having 10 digit characters?
|
|
|
|
|
SSCommitted
      
Group: General Forum Members
Last Login: Tuesday, May 28, 2013 3:05 AM
Points: 1,943,
Visits: 8,229
|
|
At a guess , that is the number of seconds from 01jan1970
try this
select (1231151827/60) select 20519197/60 select 341986/24 select dateadd(dd,14249,'19700101')
Clear Sky SQL My Blog Kent user group
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231,
Visits: 3,483
|
|
Hi,
try this
select convert(datetime,40010/*your int value*/,112/*your format*/)
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Thursday, April 19, 2012 10:25 PM
Points: 1,231,
Visits: 3,483
|
|
DATETIME to INT
declare @time1 datetime set @time1 = getdate() select convert(int,@time1) print @time1 INT to DATETIME
declare @time int set @time = 40042 select convert(datetime,@time,112) print @time
|
|
|
|
|
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 query
select (1231151827/60) select 20519197/60 select 341986/24 select dateadd(dd,14249,'19700101')
|
|
|
|
|
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 query
select (1231151827/60) select 20519197/60 select 341986/24 select dateadd(dd,14249,'19700101')
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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.000 select (1231151827 % 60) --sec to min gain 7 seconds from select 20519197 % 60 --min to hour gain 37 minutes select 341986 % 24 --hour to days gain 10 hours
select dateadd(second,1231151827,'19700101') --2009-01-05 10:37:07.000
Lowell
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Monday, May 13, 2013 11:50 AM
Points: 141,
Visits: 275
|
|
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
|
|
|
|
|
SSChampion
        
Group: General Forum Members
Last Login: Today @ 4:22 PM
Points: 11,789,
Visits: 28,063
|
|
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
--There is no spoon, and there's no default ORDER BY in sql server either. Actually, Common Sense is so rare, it should be considered a Superpower. --my son
|
|
|
|