UTC to datetime

[amrit09]

Hi all

I am currently running SQL server 2005. I have UTCs stored as a bigint and wish to convert these into a datetime. I am using:-

SELECT DATEADD(mm, F1, '01/01/1970 00:00:00')

The number coming from the F1 column is a 13digit number. When i run this query, i get the error:-

Arithmetic overflow error converting expression to data type int.

When i manually put in a number from the column, this still doesn't work, but, when i reduce the number to 10 digits the conversion does work.

Can anyone help with getting the full number to convert?

thanks
AMRIT

[azimmer]

Hi all

I am currently running SQL server 2005. I have UTCs stored as a bigint and wish to convert these into a datetime. I am using:-

SELECT DATEADD(mm, F1, '01/01/1970 00:00:00')

The number coming from the F1 column is a 13digit number. When i run this query, i get the error:-

Arithmetic overflow error converting expression to data type int.

When i manually put in a number from the column, this still doesn't work, but, when i reduce the number to 10 digits the conversion does work.

Can anyone help with getting the full number to convert?

thanks
AMRIT

Firstly I suspect you meant:

SELECT     DATEADD(ms, F1, '01/01/1970 00:00:00')
 

I would consider converting F1 to a longer timescale first and then add it, like this (if F1 is indeed milliseconds):

SELECT     DATEADD(s, F1/1000, '01/01/1970 00:00:00')
 

[amrit09]

Thanks, will try it

AMRIT