SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Time as bigint
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 05/27/2003 :  13:50:08  Show Profile  Reply with Quote
Hi,

I have columns that represent time, but data type is bigint and data looks like 126974263160000000.

Is there any way to make it looking as datetime, or something else that displays hours, days, etc?

Thx,

Dobby

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 05/27/2003 :  13:58:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
Have a look at CAST or CONVERT in Books Online. Not sure if your data will easily convert though. You might need to format it first. What time does this represent: 126974263160000000?

Tara
Go to Top of Page

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 05/27/2003 :  15:22:07  Show Profile  Reply with Quote
When using cast or convert the error message is :

Arithmetic overflow error convertimg expression to data type datatime.

Dobby
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/27/2003 :  15:25:51  Show Profile  Reply with Quote
Well....

The following:

DECLARE @x bigint
SELECT @x = 126974263160000000
SELECT CONVERT(datetime,@x,104)

Gets you:

Server: Msg 8115, Level 16, State 2, Line 3
Arithmetic overflow error converting expression to data type datetime.

Can you show what that's suppose to look like as a valid datatime value?

Is it suppose to be:

12/6/97 4:26:31 600?



Brett

8-)
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 05/27/2003 :  15:26:04  Show Profile  Visit tkizer's Homepage  Reply with Quote
We need to see your T-SQL. Also, how does your data convert to a time value? Arithmetic overflow means that your data is outside of the range that the datatype can handle. Your data probably just needs to be formatted correctly before the convert.

Tara
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 05/27/2003 :  15:39:06  Show Profile  Reply with Quote
quote:

Is it suppose to be:

12/6/97 4:26:31 600?




I wondered if it was the number of microseconds since some time 4000-odd years ago, but I couldn't think of anything that happened then.


Edited by - Arnold Fribble on 05/27/2003 15:56:32
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/27/2003 :  16:08:13  Show Profile  Reply with Quote
How about the advent of the bronze age:

http://www.bbc.co.uk/history/timelines/ni/metal_workers.shtml



Brett

8-)
Go to Top of Page

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 05/27/2003 :  16:25:13  Show Profile  Reply with Quote
The properties of table indicate type: bigint, size 8.

The only corespondence a have is:

FirstDate
5/14/2003 4:51:56 PM

UTCFirstDate
126974047160000000

LastDate
5/22/2003 4:24:05 PM

UTCLastDate
126980942448130000

An apptilication writes to the DB and displays data as datetime.
I'm looking to look into tables directly.
The bigint are seconds after 1970... I'm not sure!

Thx,

Dobby

Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 05/27/2003 :  16:30:13  Show Profile  Visit tkizer's Homepage  Reply with Quote
quote:

An apptilication writes to the DB and displays data as datetime.
I'm looking to look into tables directly.



If your application is able to display the data as datetime, then just borrow that code (probably will have to be rewritten for T-SQL though).

quote:

The bigint are seconds after 1970... I'm not sure!



If you don't know what this data represents, then it is impossible for us to help with this one. Please post the code that the application uses and we'll see if we can help.

Tara
Go to Top of Page

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 05/27/2003 :  17:20:00  Show Profile  Reply with Quote
If you only give me a hint how and where from to get that code?

Dobby
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36984 Posts

Posted - 05/27/2003 :  18:15:40  Show Profile  Visit tkizer's Homepage  Reply with Quote
Can't you get the code from the developer of the application?

Tara
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 05/28/2003 :  04:48:29  Show Profile  Reply with Quote
quote:

FirstDate
5/14/2003 4:51:56 PM

UTCFirstDate
126974047160000000

LastDate
5/22/2003 4:24:05 PM

UTCLastDate
126980942448130000



Ah, that looks more plausible. I'm not sure what the last 4 digits are (though I have a nasty suspicion they might be a timezone -- ick!), but the rest looks like milliseconds since 1601... Edit: Aha, it's a Win32 FILETIME structure: that stores the date as 100-nanosecond intervals since 1601 in which case your "4:24:05" is actually a rounded-up 16:24:04.813.
FILETIME assumes the Gregorian calendar from its origin, but it's probably best to take a known date within SQL Server's datetime era -- I've used 19000101 here just because that's the zero point for converting integers to datetime.

Note that this code truncates the sub-millisecond part of the date

DECLARE @dt AS bigint, @day AS int, @ms AS int
SET @dt = 126974047160000000
SET @ms = (@dt / CAST(10000 AS bigint)) % 86400000
SET @day = @dt / CAST(864000000000 AS bigint) - 109207
SELECT DATEADD(ms, @ms, DATEADD(day, @day, 0))

SET @dt = 126980942448130000
SET @ms = (@dt / CAST(10000 AS bigint)) % 86400000
SET @day = @dt / CAST(864000000000 AS bigint) - 109207
SELECT DATEADD(ms, @ms, DATEADD(day, @day, 0))

 
or putting it together in one expression:

DECLARE @dt AS bigint
SET @dt = 126974047160000000
SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

SET @dt = 126980942448130000
SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

 
http://msdn.microsoft.com/library/en-us/sysinfo/base/filetime_str.asp


Edited by - Arnold Fribble on 05/28/2003 06:11:05
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/28/2003 :  11:07:56  Show Profile  Reply with Quote
WHO DA MAN!!!!!!!!!!

DECLARE @dt AS bigint
SET @dt = 126974263160000000
SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

Returns:

------------------------------------------------------
2003-05-14 22:51:56.000

(1 row(s) affected)

nemohm, is that the right answer?



Brett

8-)
Go to Top of Page

nemohm
Yak Posting Veteran

Canada
69 Posts

Posted - 05/30/2003 :  15:22:06  Show Profile  Reply with Quote
Thank you gentlemen!

Dobby
Go to Top of Page

mbruner_earj
Starting Member

Brazil
6 Posts

Posted - 11/10/2005 :  11:57:42  Show Profile  Reply with Quote
I have a value of 1122433200000 that corresponds to 2005-07-26 22:00:00.0

I tried the Queries that you guys show above, but I still get the following error.

Server: Msg 517, Level 16, State 1, Line 4
Adding a value to a 'datetime' column caused overflow.

My query looks like the following:

DECLARE @dt AS bigint
SET @dt = 1129514400000

SELECT DATEADD(ms, (@dt / CAST(10000 AS bigint)) % 86400000,
DATEADD(day, @dt / CAST(864000000000 AS bigint) - 109207, 0))

Can someone help me out here? Thanks.

Michael
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 11/10/2005 :  12:08:02  Show Profile  Reply with Quote
Michael, since your dates aren't FILETIME values, I'll answer this question in original thread, rather than pollute this one.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000