Date as Binary(8) - 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

Old Forums

CLOSED - General SQL Server

Date as Binary(8)

Author

Topic

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 09:12:01

I've inherited (yet another) server, supporting a third party (disgusting) product called synergy/watermark.

The stuff a date (I don't know how yet) into a binary(8) column.

I was assuming something like:


USE Northwind
GO

CREATE TABLE myTable99 (col1 binary(8))
GO

INSERT INTO myTable99(col1)
SELECT CONVERT(binary(8),'01012003')

SELECT STUFF(STUFF(CONVERT(varchar(8),col1),3,0,'/'),6,0,'/') FROM myTable99
GO

DROP TABLE myTable99
GO

But that's not it..

I'm working on other coversions right now...playing with INT ect.

Sample data stored as:

dateCreated
------------------
0x01C1CC57798F2590
0x01BCE00FC55F0000
0x01BD9982DEDC0000
0x01BD7D38E7FD0000
0x01BF66B3B0BA4600
0x01BEB8EE5DBCE3E0
0x01BBA36200E5C000
0x01BD8514901E8000
0x01C16890DD9FA0D0
0x01BD0E6C8BBE4000
0x01BEB2A038480CB0
0x01BDAACC83F28000
0x01BBAB3DA9074000

There's 1.5 million rows in this table

WHY they did binary(8) is beyond me. Isn't that 8 bytes for the binary + 4 for the overhead.

Isn't dattetime 8 bytes itself internally. Doesn't 8 beat 12?

Any insight on the algorithym they're employing would be a great help.

Brett

8-)

efelito
Constraint Violating Yak Guru

478 Posts

Posted - 2003-07-17 : 09:30:14

No insight as to why someone would do this... probably just feeling a little creative that day or read something on their box of Fruit Loops that morning that said it was faster.

When your attempting conversions, check to see if the dates you are coming up with are all off by the same amount. Maybe they use a base date to do their calculations much like SQL Servers "1900-01-01 00:00:00.000".

Is it possible to just contact the vendor to find out what they are doing?

Jeff Banschbach, MCDBA

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 09:36:55

Thanks Jeff (fruit loops indeed).

I tried a conversion of EVERY datatype and the only thing that da any "meaning" (if that's allowed here) is int.

It gives me:

2039424400
-983629824
-556007424
-402849792
-1329969664
1572660192
15056896
-1877049344
-576741168
-1950466048

But again is it less effecient than just a plain date? Also, if infer correctly, that binary(8) would be mmddccyy? So even less effiecient than datetime?

Man, if I was allowed to get away with this nonesense....

Brett

8-)

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-17 : 10:56:59

Short memory, Brett. They're Win32 FILETIME values.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=26544

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 11:27:17

I knew you'd be the man.

I remeber now that you mention it, but I've had very little (0 is a number right) experience with it.

OK, now more to the point, Why did they do this?

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 12:35:51

Great..

This sucker is in SQL 7.0

No bigint yet...

thought I'd try real or float, but I thought they threw an error already...

yup...it just threw(up) all over

what the hell is modulo?

quote:

Server: Msg 403, Level 16, State 1, Line 3
Invalid operator for data type. Operator equals modulo, type equals real.

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 12:37:49

Never mind...

BOL 7.0

quote:

The modulo arithmetic operator can be used in the select list of the SELECT statement with any combination of column names, numeric constants, or any valid expression of the integer data type category.

But what to do about bigint?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-07-17 : 13:51:36

Brett --
show us some of the code you are trying to execute that is causing these errors, and what you are trying to do ...

there's 4 bytes of overhead for binaries ??

- Jeff

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 13:59:23

BOL:

quote:

binary [ ( n ) ]

Fixed-length binary data of n bytes. n must be a value from 1 through 8,000. Storage size is n+4 bytes.

Unless I'm reading that wrong...

The code is what the frib-mister put out a while ago in the thread listed above, but it uses bigint, which didn't come alive until 2k.

The code I'm working with is just like arnolds, but the local var is replaced with a column name.

This has to run in 7.0

I think it's a prentation layer issue though.

But I still go back to why would anyone do this?

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-17 : 14:59:21

b/c they weren't database developers and didn't know any better, likely.

Jonathan
{0}

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-07-17 : 15:07:07

This is a bit imprecise since it casts float to datetime, but it's only out by 1 tick (3ms) on any of the values given... which presumably is of no practical interest (unless it drops back over a day boundary...)
Assuming d is the binary(8) column:


CAST(
((CAST(CAST(SUBSTRING(d, 1, 2) AS int) AS float) * 16777216.0 +
   CAST(CAST(SUBSTRING(d, 3, 3) AS int) AS float)) * 16777216.0 +
  CAST(CAST(SUBSTRING(d, 6, 3) AS int) AS float))
/ 864000000000.0 - 109207.0 as datetime)

I suppose I better add a caveat that I don't have a version 7.0 server handy, so this was only tried out on 2000.

Edited by - Arnold Fribble on 07/17/2003 15:11:22

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 15:12:58

Well I'm off the hook....

The developers ran with the pages Arnold identified at the MS site, they had never seen this before. So it'll all be done in the presentation layer.

Thanks Everyone.

Thanks esp to the Frib-miester. Thanks Arnold.

Brett

8-)

setbasedisthetruepath
Used SQL Salesman

992 Posts

Posted - 2003-07-17 : 15:25:25

quote:
So it'll all be done in the presentation layer.

Shout it from the hilltops ...

Jonathan
{0}

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-17 : 15:43:21

Holy Sh-t!

Arnold, you are

DA MAN

Works like a champ on 7.0...

How?

I'm at a loss....I bow before greatness...

Was there a Greek or Roman god that was the god of time?

Because THAT should be your custom...

Brett

8-)

X002548
Not Just a Number

15586 Posts

Posted - 2003-07-18 : 08:53:29

OF COURSE!

Arnold's custom should be...

drumroll please...

SQL Cronos GOD!

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources