How to convert the integer value to datetime

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » How to convert the integer value to datetime

How to convert the integer value to datetime

Rate Topic

Display Mode

Topic Options

Author

Message

sanjay.dakolia

Posted Monday, October 22, 2012 6:18 AM

Forum 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

Lowell

Posted Monday, October 22, 2012 6:21 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 11,614, Visits: 27,673

sanjay.dakolia (10/22/2012)

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

sanjay.dakolia

Posted Monday, October 22, 2012 6:24 AM

Forum 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

ChrisM@home

Posted Monday, October 22, 2012 6:30 AM

SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 11:25 PM
Points: 920, Visits: 3,732

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

Lowell

Posted Monday, October 22, 2012 6:30 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 6:06 AM
Points: 11,614, Visits: 27,673

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

sanjay.dakolia

Posted Monday, October 22, 2012 6:33 AM

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

Permissions