Time as bigint - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Time as bigint

Author

Topic

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-27 : 13:50:08

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

38200 Posts

Posted - 2003-05-27 : 13:58:45

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

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-27 : 15:22:07

When using cast or convert the error message is :

Arithmetic overflow error convertimg expression to data type datatime.

Dobby

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 15:25:51

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-)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 15:26:04

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-27 : 15:39:06

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

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 16:08:13

How about the advent of the bronze age:

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

Brett

8-)

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-27 : 16:25:13

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

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 16:30:13

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

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-27 : 17:20:00

If you only give me a hint how and where from to get that code?

Dobby

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2003-05-27 : 18:15:40

Can't you get the code from the developer of the application?

Tara

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-05-28 : 04:48:29

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

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-28 : 11:07:56

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-)

nemohm
Yak Posting Veteran

69 Posts

Posted - 2003-05-30 : 15:22:06

Thank you gentlemen!

Dobby

mbruner_earj
Starting Member

6 Posts

Posted - 2005-11-10 : 11:57:42

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

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2005-11-10 : 12:08:02

Michael, since your dates aren't FILETIME values, I'll answer this question in original thread, rather than pollute this one.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources