Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Convertion of integer to Datetime format Expand / Collapse
Author
Message
Posted Tuesday, August 18, 2009 2:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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?
Post #772490
Posted Tuesday, August 18, 2009 2:55 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:57 AM
Points: 1,949, Visits: 8,311
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
Post #772496
Posted Tuesday, August 18, 2009 2:59 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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*/)

Post #772499
Posted Tuesday, August 18, 2009 3:04 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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

Post #772504
Posted Tuesday, August 18, 2009 4:49 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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')
Post #772551
Posted Tuesday, August 18, 2009 4:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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')
Post #772554
Posted Tuesday, August 18, 2009 6:11 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 12,909, Visits: 32,009
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
Post #772606
Posted Wednesday, August 19, 2009 9:14 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, June 6, 2014 5:04 AM
Points: 147, Visits: 301
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

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:48 PM
Points: 12,909, Visits: 32,009
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
Post #773654
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse