Convert UTC BIGINT to DATETIME

Transact-SQL
1

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?

2

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

3

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

4

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
5

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

6
7

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
8

This link may help you..

Please check it out

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

Screenshot_2019-06-02-16-44-06
Screenshot_2019-06-02-16-44-06.jpeg800×1280 396 KB

1 Like
9

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