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
Medium Priority
344 Views

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

I need the precision beyond the second.

Any tips?
0
Question by:dbaSQL
• 5
• 3
• 2
• +1

LVL 17

Author Comment

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

ID: 39184632
millisecond?

0

LVL 93

Expert Comment

ID: 39184637
>> I need the precision beyond the second.

Then why are you using second as the interval?
0

LVL 93

Expert Comment

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

0

LVL 17

Author Comment

ID: 39184670
i tried ms as my interval, but my output turns into this:    1970-01-16 20:18:07.430

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

ID: 39184686
millisecond  ms
microsecond mcs
nanosecond ns
0

LVL 17

Author Comment

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

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

returns: 1970-01-16 20:18:07.430
0

LVL 17

Author Comment

ID: 39184714
do you have a suggestion?
0

LVL 46

Accepted Solution

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

Kent
0

LVL 49

Assisted Solution

PortletPaul earned 1000 total points
ID: 39184733
select
)
, convert(varchar,
)
, 14)

beat me to it :(
0

LVL 17

Author Closing Comment

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

I used this:

Thank you both for your time.
0

## Featured Post

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 â€¦
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
Course of the Month14 days, 2 hours left to enroll