• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 790
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

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