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

How to convert the integer value to datetime Expand / Collapse
Author
Message
Posted Monday, October 22, 2012 6:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17
hi i am new to sql and i have the following problem
i am using the following query :
select cast (1157068800 as datetime)
but i am getting this error :
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

please help
thanks
sanjay
Post #1375369
Posted Monday, October 22, 2012 6:21 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
sanjay.dakolia (10/22/2012)
hi i am new to sql and i have the following problem
i am using the following query :
select cast (1157068800 as datetime)
but i am getting this error :
Msg 8115, Level 16, State 2, Line 1
Arithmetic overflow error converting expression to data type datetime.

please help
thanks
sanjay


well it depends on what 1157068800 *means* doesn't it?
that looks a lot like a unix date, which is the # seconds since 01/01/1970:
/*
declare @NumSeconds int
SET @NumSeconds = 1157068800

select dateadd(second,@NumSeconds,'19700101')
*/


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 #1375374
Posted Monday, October 22, 2012 6:24 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17
this value was suppose to be datetime but by mistake the column datatype was given integer so now i want to convert it back to datetime
Post #1375379
Posted Monday, October 22, 2012 6:30 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 4:53 AM
Points: 1,090, Visits: 6,539
sanjay.dakolia (10/22/2012)
this value was suppose to be datetime but by mistake the column datatype was given integer so now i want to convert it back to datetime


What date does 1157068800 represent?



Low-hanging fruit picker and defender of the moggies





For better assistance in answering your questions, please read this.




Understanding and using APPLY, (I) and (II) Paul White

Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Post #1375383
Posted Monday, October 22, 2012 6:30 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Friday, August 29, 2014 7:44 AM
Points: 12,910, Visits: 32,015
sanjay.dakolia (10/22/2012)
this value was suppose to be datetime but by mistake the column datatype was given integer so now i want to convert it back to datetime

um, i don't think so.

if it was a direct convert to integer from SQL Server, the number would be 38959 as far as SQl server is concerned.

pretty sure my first guess is correct; it resolves to
'2006-09-01 00:00:00.000', so i'm pretty sure it was imported form another system as is, and was never a datetime in SQL that was accidentally stored in an integer column.

--returns 38959 
select CONVERT(decimal,convert(datetime,'2006-09-01 00:00:00.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 #1375384
Posted Monday, October 22, 2012 6:33 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, December 16, 2012 11:53 PM
Points: 9, Visits: 17
thanks a ton Lowell it worked
Post #1375386
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse