SQL Dateadd is rounding decimal on seconds

Posted on 2012-08-21
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.

Question by:CodeRooster
    LVL 142

    Expert Comment

    by: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 ...

    Author Comment

    I tried that but I get this error -

    Arithmetic overflow error converting expression to data type int.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    ok, let me start up my VM with SQL server on it and check it out ...
    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

    Open in new window

    --------------------- -----------------------
    1061156877,322        2012-08-16 21:47:57.323

    Author Comment

    Very nice.  That worked perfectly.  Thank you much!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    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.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now