Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Convert BIGINT to DATETIME, but losing precision

Posted on 2013-05-21
11
Medium Priority
?
344 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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

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 …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
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.
Suggested Courses

580 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