Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««123»»

Convert Bigint time to datetime Expand / Collapse
Author
Message
Posted Thursday, April 17, 2014 11:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 5:22 AM
Points: 300, Visits: 1,070

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))

worked for me!
Post #1562786
Posted Thursday, April 17, 2014 11:09 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 20,735, Visits: 32,517
PSB (4/17/2014)

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 1397750400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))

worked for me!


So the BIGINT value was actually milliseconds since midnight 1970-01-01, not minutes. Good to know.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1562788
Posted Thursday, April 17, 2014 11:13 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
Something to consider:

select datediff(m, '0001-01-01', '9999-12-31')

returns 119987

That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.

Is 1397750400000 the minute of the Big Bang or something like that?
Post #1562790
Posted Thursday, April 17, 2014 11:41 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 10:01 AM
Points: 10,282, Visits: 13,266
gbritton1 (4/17/2014)
Something to consider:

select datediff(m, '0001-01-01', '9999-12-31')

returns 119987

That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.

Is 1397750400000 the minute of the Big Bang or something like that?


the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation.




Jack Corbett

Applications Developer

Don't let the good be the enemy of the best. -- Paul Fleming

Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
How to Post Performance Problems
Crosstabs and Pivots or How to turn rows into columns Part 1
Crosstabs and Pivots or How to turn rows into columns Part 2
Post #1562800
Posted Thursday, April 17, 2014 12:28 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Sunday, October 12, 2014 4:00 PM
Points: 341, Visits: 751
Sorry, you're quite right! That should be:

declare @maxminutes bigint = datediff(day, '0001-01-01', '9999-12-31')*cast(24*60 as bigint)
select @maxminutes

which yields: 5258963520
Post #1562814
Posted Sunday, April 20, 2014 4:24 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 10:21 AM
Points: 2,220, Visits: 6,004
PSB (4/17/2014)
Minutes


1397750400000 Minutes
23295840000 Hours
970660000 Days
2657522 Years


The first birthday of Homo habilis?
Post #1563322
Posted Sunday, April 20, 2014 5:33 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 11:04 AM
Points: 20,735, Visits: 32,517
Eirikur Eiriksson (4/20/2014)
PSB (4/17/2014)
Minutes


1397750400000 Minutes
23295840000 Hours
970660000 Days
2657522 Years


The first birthday of Homo habilis?


Actually, it was milliseconds.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1563325
Posted Sunday, April 20, 2014 6:33 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
gbritton1 (4/17/2014)
Just do it in a loop, ...


I hope your not serious. Let's see the code that does it in a loop.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563374
Posted Sunday, April 20, 2014 6:35 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 8:08 AM
Points: 35,366, Visits: 31,906
Jack Corbett (4/17/2014)
gbritton1 (4/17/2014)
Something to consider:

select datediff(m, '0001-01-01', '9999-12-31')

returns 119987

That is the largest difference in minutes between the earliest and latest dates handled by SQL Server. The earliest date it can store is Jan 1 1753, IIRC.

Is 1397750400000 the minute of the Big Bang or something like that?


the "m" is MONTH not minute, the shorthand for minute is mi or n. That's why I always spell out the full identifier: MONTH, YEAR, DAY, MINUTE, HOUR, SECOND, MILLISECOND instead of the abbreviation.


Heh... oddly enough, that's why I use the 2 character abbreviations for dateparts.


--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1563375
Posted Monday, April 21, 2014 5:58 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 5:22 AM
Points: 300, Visits: 1,070
I have a couple of 14 digit int time in my table

SELECT DATEADD(hh,-5,dateadd(s, convert(bigint, 61353491400000) / 1000, convert(datetime, '1-1-1970 00:00:00')))

The above query throws an error :

Arithmetic overflow error converting expression to data type int.

Please advise how di I resolve it.

Thanks,
PSB
Post #1563443
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse