BigInt to date time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

BigInt to date time

Author

Topic

pratecantej
Starting Member

3 Posts

Posted - 2011-07-27 : 09:26:33

I have seen a lot of replies to this one and i have searched everywhere for a solution, including the sample code that i have found but no luck.

My issue is with a datetime that is stored in a BigInt and looks like this, 634473650185170000 this represents 7/27/2011 8:03:38 AM.
I have used this statement with limited success, bare in mind that i'm new at this.
select
Timebegin =
dateadd(minute,(convert(bigint,timebegin)-47966688000000000)/600000000.0,'1753')
from
bookmarks;
this returns the value 3611-07-27 12:03:00
the year is off by 15 years and the time is in UTC time i think.

Can anyone shed some light on this one. The sample code came from a website while doing research on google.

Thank you.

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2011-07-27 : 09:40:49

I would recommend that you reference the documentation or the developer(company) that is using the bigint to let you know how it represents said date. Once you have the formula... it shouldn't be an issue to help you implement it.

Another possible method, would be to get several datetimes with the respective bigint representation... and work backwords. But that's a pain.

Corey

I Has Returned!!

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2011-07-27 : 14:11:48

>> My issue is with a DATETIME(0) that is stored in a BIGINT and looks like this, 634473650185170000 this represents 7/27/2011 8:03:38 AM. <<

No, the correct format in SQL would be '2011-07-07 08:03:38' ; read your ANSI/ISO SQL Standards – you do keep a copy on your desk, don't you? I also hope you have a gun, so you can shot the guy that did this to you.

I have used this statement with limited success, bare in mind that I’m new at this.
SELECT DATEADD(MINUTE,(CAST(something_begin_datetime AS BIGINT)
-47966688000000000)/600000000.0,'1753')
AS something_begin_datetime
FROM Bookmarks;

>> this returns the value 3611-07-27 12:03:00 <<

How? We need a CAST (?? AS DATETIME(0) somewhere in this.

>> the year is off by 15 years and the time is in UTC time I think. Can anyone shed some light on this one. The sample code came from a website while doing research on Google. <<

What exactly is this “magic number”? We used to have a clock cycle counter in UNIX, but this does not look like that. Astronomer's Julian number?

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

pratecantej
Starting Member

3 Posts

Posted - 2011-07-28 : 11:15:40

UPDATE:
OK i have found something else that could shed some light on the BigInt number. This code works fine for testing.
Declare @TickValue bigint
Declare @Days float

Set @TickValue = 634473856009391548
Select @Days = timebegin * POWER(10.00000000000,-7) / 60 / 60 / 24

Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE))
+ Cast( (@Days - FLOOR(@Days)) As DateTime)
this returns the date of 2011-07-22 17:55:15
Now i don't know how to replace the @TcickValue with date from the database.
I have tried replacing @TcickValue with value from the DB like this
Declare @TickValue bigint
Declare @Days float

Set @TickValue = 634473856009391548
Select @Days = timebegin * POWER(10.00000000000,-7) / 60 / 60 / 24 from bookmarks

Select DATEADD(d, Cast(@Days As int), Cast('0001-01-01' As DATE))
+ Cast( (@Days - FLOOR(@Days)) As DateTime)
but it only returns one record.

pratecantej
Starting Member

3 Posts

Posted - 2011-08-11 : 09:56:40

Since no one replied i tried something else and this is what i'm getting now with this code.

Declare @TickValue bigint
Declare @Start float
Declare @End float
Declare @date datetime
Declare @enddate datetime
Declare @count int

Set @count = (select Count(*) from bookmarks);

Select @Start = timebegin * POWER(10.00000000000,-7) / 60 / 60 / 24 from bookmarks;
Select @end = timeend * POWER(10.00000000000,-7) / 60 / 60 / 24 from bookmarks;

Select @date = DATEADD(d, Cast(@Start As int), Cast('0001-01-01' As DATE))
+ Cast( (@Start - FLOOR(@Start)) As DateTime)
Select @enddate = DATEADD(d, Cast(@end As int), Cast('0001-01-01' As DATE))
+ Cast( (@end - FLOOR(@end)) As DateTime)

select timebegin, user "User ID", Description "Book Mark Description", @date "Start Date/Time",@enddate "End Date/Time" from bookmarks;

timebegin User ID Book Mark Description Start Date/Time End Date/Time
634472070334724492 johnp 2011-07-22 17:55:15.483 2011-07-22 17:55:48.487
634473856009391548 johnp asdfasdfasdf 2011-07-22 17:55:15.483 2011-07-22 17:55:48.487
634469343413278290 johnp 2011-07-22 17:55:15.483 2011-07-22 17:55:48.487
634469343464290380 johnp 2011-07-22 17:55:15.483 2011-07-22 17:55:48.487

there are 32 rows in the table but for some reason it's only using the value from the last record to calculate the date.
Any help would be appreciated.

Thank you.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources