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 9:29 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:21 AM
Points: 296, Visits: 1,058
Hi,

How to convert bigint time to datetime (CST)

Declare @MyBigIntTime BIGINT =1397750400000

Thanks,
PSB
Post #1562717
Posted Thursday, April 17, 2014 9:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
What units are your bigint in? Minutes, seconds, hours, years, microseconds, months ,picoseconds?
Post #1562734
Posted Thursday, April 17, 2014 9:49 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:21 AM
Points: 296, Visits: 1,058
Minutes
Post #1562738
Posted Thursday, April 17, 2014 10:01 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
Also, what date and time does the value provided equate?



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 #1562742
Posted Thursday, April 17, 2014 10:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Yesterday @ 2:42 PM
Points: 10,193, Visits: 13,120
What is the base date? 1900-01-01, 1970-01-01. The basic idea is:

DATEADD(Minute, Integer, BaseDate)




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 #1562761
Posted Thursday, April 17, 2014 10:35 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
Unfortunately, you get an overflow error when trying to convert the BIGINT value provided to an INT value when using DATEADD.



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 #1562765
Posted Thursday, April 17, 2014 10:43 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, September 11, 2014 6:27 AM
Points: 333, Visits: 742
Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.
Post #1562773
Posted Thursday, April 17, 2014 10:47 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
gbritton1 (4/17/2014)
Well, OK. Just do it in a loop, adding portions of bigint that fit into an integer to an accumulating date field.


Okay, using what for a base? The OP still hasn't told us what the value provided represents.



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 #1562775
Posted Thursday, April 17, 2014 10:48 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 5:21 AM
Points: 296, Visits: 1,058

select DATEADD(Minute, 1397750400000, '1970-00-01')
Getting an overflow error .


Arithmetic overflow error converting expression to data type int.
Post #1562776
Posted Thursday, April 17, 2014 10:49 AM


SSC-Insane

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

Group: General Forum Members
Last Login: Yesterday @ 7:22 PM
Points: 20,680, Visits: 32,279
PSB (4/17/2014)

select DATEADD(Minute, 1397750400000, '1970-00-01')
Getting an overflow error .


Arithmetic overflow error converting expression to data type int.


Yep, told you that would happen earlier.

Now, what date and time does 1397750400000 represent??



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 #1562777
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse