asked on

SQL Dateadd is rounding decimal on seconds

I have a "Time" column as a money datatype. It represents seconds since Epoch (1970-01-01).
I am converting this to a datetime by doing this -

Dateadd(ss, Time, '1979-01-01')

So, when Time is 1345521925.35 seconds, the result should be 2012-08-21 04:05:25.350. It ends up being 2012-08-21 04:05:25.000 with the decimal on the seconds rounded. I need the fraction to stay. Any ideas? I tried doing a dateadd with ms and multiplying Time by 1000 but I get an error -

Arithmetic overflow error converting expression to data type int.

I am on SQL Server 2012. Any ideas would be appreciated.

Thanks!

Guy Hengel [angelIII / a3]

>I have a "Time" column as a money datatype. It represents seconds since Epoch (1970-01-01).
wondering why you do that?

>It represents seconds since Epoch
I presume including fractions of seconds also. ..

then, I would take your code, and get the decimal value, multiply that by 1000, and use that in the dateadd with milliseconds ...

CodeRooster

ASKER

I tried that but I get this error -

Arithmetic overflow error converting expression to data type int.

Guy Hengel [angelIII / a3]

ok, let me start up my VM with SQL server on it and check it out ...

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

CodeRooster

ASKER

Very nice. That worked perfectly. Thank you much!