Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Convert BIGINT to DATETIME, but losing precision

Posted on 2013-05-21
11
Medium Priority
?
341 Views
Last Modified: 2013-05-21
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?
0
Comment
Question by:dbaSQL
  • 5
  • 3
  • 2
  • +1
11 Comments
 
LVL 17

Author Comment

by:dbaSQL
ID: 39184619
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.
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39184632
millisecond?

select DATEADD(millisecond,(1369087430101949/1000000000),'1970-01-01')
0
 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39184637
>> I need the precision beyond the second.

Then why are you using second as the interval?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 93

Expert Comment

by:Patrick Matthews
ID: 39184639
Also, you are doing integer division.  Try:

select DATEADD(millisecond,(1.0*1369087430101949/1000000000),'1970-01-01')
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39184670
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
0
 
LVL 49

Expert Comment

by:PortletPaul
ID: 39184686
dateadd supports these:
millisecond  ms
microsecond mcs
nanosecond ns
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39184692
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
0
 
LVL 17

Author Comment

by:dbaSQL
ID: 39184714
do you have a suggestion?
0
 
LVL 46

Accepted Solution

by:
Kent Olsen earned 1000 total points
ID: 39184726
I'm not able to convert the value directly into a datetime at the millisecond level as I keep getting an overflow condition.  But I can convert the value to the datetime that you're getting, and add the milliseconds in as a separate call.

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



Kent
0
 
LVL 49

Assisted Solution

by:PortletPaul
PortletPaul earned 1000 total points
ID: 39184733
select
dateadd(ms,(1369087430101949 % 1000000),
dateadd(s,round(1369087430101949 / 1000000,0),'1970-01-01')
        )
, convert(varchar,
          dateadd(ms,(1369087430101949 % 1000000),
dateadd(s,round(1369087430101949 / 1000000,0),'1970-01-01')
        )
, 14)

beat me to it :(
0
 
LVL 17

Author Closing Comment

by:dbaSQL
ID: 39184886
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.
0

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

916 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