Link to home
Create AccountLog in
Avatar of dbaSQL
dbaSQLFlag for United States of America

asked on

Convert BIGINT to DATETIME, but losing precision

select DATEADD(s,(1369087430101949/1000000),'1/1/1970')

this returns:  2013-05-20 22:03:50.000

I need the precision beyond the second.  

Any tips?
Avatar of dbaSQL
dbaSQL
Flag of United States of America image

ASKER

To clarify, I am converting the BIGINT to DATETIME, but I am losing the precision in my conversion.  I need to prevent that loss of precision.
Avatar of PortletPaul
millisecond?

select DATEADD(millisecond,(1369087430101949/1000000000),'1970-01-01')
>> I need the precision beyond the second.

Then why are you using second as the interval?
Also, you are doing integer division.  Try:

select DATEADD(millisecond,(1.0*1369087430101949/1000000000),'1970-01-01')
Avatar of dbaSQL

ASKER

i tried ms as my interval, but my output turns into this:    1970-01-16 20:18:07.430
select DATEADD(ms,(1369087430101949/1000000),'1/1/1970')

I tried your suggestion, matthews, but it returns this:
1970-01-01 00:22:49.087

Paul, yours does the same -- 1970-01-01 00:22:49.087
dateadd supports these:
millisecond  ms
microsecond mcs
nanosecond ns
Avatar of dbaSQL

ASKER

yes.  but the output with ms is 1970, rather than 5/20.

select DATEADD(s,(1369087430101949/1000000),'1/1/1970')
returns:  2013-05-20 22:03:50.000

select DATEADD(ms,(1369087430101949/1000000),'1/1/1970')
returns: 1970-01-16 20:18:07.430
Avatar of dbaSQL

ASKER

do you have a suggestion?
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
SOLUTION
Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of dbaSQL

ASKER

A little closer, but both of yours were only giving me millis, not micros.  

I used this:
SELECT DATEADD (mcs,(1369087430101949 % 1000000),CAST(DATEADD(s,(1369087430101949/1000000), '1/1/1970') AS DATETIME2))

Thank you both for your time.