Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Convert BIGINT to DATETIME, but losing precision

Posted on 2013-05-21
11
Medium Priority
?
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

688 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