Solved

# SQL Dateadd is rounding decimal on seconds

Posted on 2012-08-21
690 Views
Last Modified: 2012-08-22
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!
0
Question by:CodeRooster
5 Comments

LVL 142

Expert Comment

>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 ...
0

Author Comment

I tried that but I get this error -

Arithmetic overflow error converting expression to data type int.
0

LVL 142

Expert Comment

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

LVL 142

Accepted Solution

this works for me:
``````declare @t table ( [time] money )

insert into @t values ( 1061156877.322 )

select t.[time]
, dateadd(ms, 1000 * (t.[Time] - floor(t.[Time])), Dateadd(ss, t.[Time], '1979-01-01'))
from @t t
``````

result:
time
--------------------- -----------------------
1061156877,322        2012-08-16 21:47:57.323
0

Author Comment

Very nice.  That worked perfectly.  Thank you much!
0

## Write Comment

Please enter a first name

Please enter a last name

We will never share this with anyone.

## Featured Post

This is my first ever Article on EE or elsewhere; therefore, please bear with me if I have some discrepancies in my writing. I read many articles and questions related to "how to pass values to SSIS packages at run-time?"  Hence, this common ques…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

#### 779 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!