SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to convert the integer value to datetime


How to convert the integer value to datetime

Author
Message
sanjay.dakolia
sanjay.dakolia
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28882 Visits: 39984
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

--
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!

sanjay.dakolia
sanjay.dakolia
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
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
ChrisM@home
ChrisM@home
SSCrazy
SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)SSCrazy (2.1K reputation)

Group: General Forum Members
Points: 2099 Visits: 10387
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
Lowell
Lowell
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28882 Visits: 39984
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

--
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!

sanjay.dakolia
sanjay.dakolia
Forum Newbie
Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)Forum Newbie (9 reputation)

Group: General Forum Members
Points: 9 Visits: 17
thanks a ton Lowell it worked
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search