Convert BigInt to seconds or milli-seconds or nano-seconds

Question

Post reply

Convert BigInt to seconds or milli-seconds or nano-seconds

AndrewSQLDBA

SSC-Insane

Points: 21827
More actions
December 12, 2013 at 9:53 am

#285945

Hello Everyone
I hope that everyone is well.
I found a rather odd column in a table that someone that did not know what they were doing has created. It is named "dur_nano", which is not even in the correct naming convention that we use, but that is another story all together.
The data type is set to bigint. This is one sample: 163691964
How can I take that value and convert it to milli-seconds and nano-seconds?
Thank You in advance for all your assistance, suggestions, and advice
Andrew SQLDBA

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

This might help you:

http://en.wikipedia.org/wiki/Orders_of_magnitude_(time)

1 second = 1,000 milliseconds

1 second = 1,000,000 microseconds

1 second = 1,000,000,000 nanoseconds

You'll keep a bigint but you can use any order of magnitude you need.

EDITED after Dwain's remark:-P

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Dwain Camps SSC Guru Points: 86978 More actions · Answer 2

Is this what you're looking for?

WITH SampleData (dur_nano) AS

(

SELECT CAST(163691964 AS BIGINT)

)

SELECT dur_nano

,millseconds=(dur_nano/1000000)

,microseconds=(dur_nano/1000)%1000

,nanonseconds=dur_nano%1000

FROM SampleData;

Note to Luis: You might want to check your conversions there my friend.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St