SQLTeam.com | Weblogs | Forums

Convert UTC BIGINT to DATETIME

I found this code in an existing SP. What exactly is a UTC in a BIGINT format? Is it a DATETIME that gets converted to BIGINT?

The following code takes a BIGINT and converts it to a DATETIME.

DECLARE @total bigint = 1559334521360;

DECLARE @seconds int = @total / 1000
SELECT @seconds
DECLARE @milliseconds int = @total % 1000;
SELECT @milliseconds

DECLARE @dteBegin datetime = '1970-1-1';
DECLARE @dteSecond datetime = DATEADD(SECOND, @seconds, @dteBegin);
DECLARE @dteMilli datetime = DATEADD(MILLISECOND, @milliseconds, @dteSecond);
SELECT @dteMilli

So why is '1970-1-1' hard coded in and what does it mean? When i change it to '1971-1-1', it gives me a different number. How did the original developer know that it should be '1970-1-1'?

So when I try to convert a DATETIME back to a BIGINT, I got this far:

SET @milliseconds = DATEDIFF(MILLISECOND, @dteSecond, @dteMilli);
SELECT @milliseconds
SET @seconds = DATEDIFF(SECOND, @dteBegin, @dteSecond);
SELECT @seconds

At this point, do I just concatenate both back together?

google 1970-1-1 and let the light shine on thee!

Already did and this is getting repetitive, seriously...

hard coding is generally not recommended

people do it for various reasons

  1. the guy who was doing it has very little experience
  2. not that important to = NOT = hard code
  3. its easier to use hard coding

these could be some of the reasons
among many many many reasons

obviously changing it to 1971-1-1
gives you a different number because

example
abc = 1 add 2 to abc = 3
if abc = 2 add 2 to abc = 4

hope this explains and helps
:slight_smile:
:slight_smile:

i love any feedback
thanks

1 Like

Hi Harish

The value (1970-1-1) that was hard coded in gave the correct answer. But that is what I don't understand why how did he know to use 1970-1-1?

Also what is utc?

Thanks

Hi BabyAqua

this is just a general discussion

in general when somebody does something
unless you have the whole picture
from him or her its hard to tell

things like context
or 1970-1-1 may have some significance
in what the whole programming is going on

WHY ?
HOW ?
in context of code
whats being done may help
maybe 1970-1-1 has some coding significance
or maybe 1970-1-1 has some BUSINESS Logic significance

i have no idea
so i am talking generally

please forgive me

hope it sheds some light
:slight_smile:
:slight_smile:

1 Like

This link may help you..

Please check it out

http://www.workableweb.com/_pages/serv_info_arch.htm

1 Like

I think what you're really looking for is "why 1970-01-01"? It's because someone used it as a default somewhere and it caught on.

And also...

2 Likes