How to convert the integer value to datetime

• sanjay.dakolia

  SSC Veteran

  Points: 249

  More actions

  October 22, 2012 at 6:18 am

  #269949

  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

  SSC Guru

  Points: 323518

  More actions

  October 22, 2012 at 6:21 am

  #1551331

  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

  SSC Veteran

  Points: 249

  More actions

  October 22, 2012 at 6:24 am

  #1551334

  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

  SSC-Insane

  Points: 24260

  More actions

  October 22, 2012 at 6:30 am

  #1551338

  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?

  ---

  [font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

  For better assistance in answering your questions, please read this[/url].

  ---

  Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

  Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 22, 2012 at 6:30 am

  #1551339

  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

  SSC Veteran

  Points: 249

  More actions

  October 22, 2012 at 6:33 am

  #1551341

  thanks a ton Lowell it worked