[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 774
  • Last Modified:

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!
0
CodeRooster
Asked:
CodeRooster
  • 3
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>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
 
CodeRoosterAuthor Commented:
I tried that but I get this error -

Arithmetic overflow error converting expression to data type int.
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
ok, let me start up my VM with SQL server on it and check it out ...
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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


result:
time                  
--------------------- -----------------------
1061156877,322        2012-08-16 21:47:57.323
0
 
CodeRoosterAuthor Commented:
Very nice.  That worked perfectly.  Thank you much!
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now