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
 Old Forums
 CLOSED - General SQL Server
 Date as Binary(8)
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  09:12:01  Show Profile
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

USA
478 Posts

Posted - 07/17/2003 :  09:30:14  Show Profile  Visit efelito's Homepage
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  09:36:55  Show Profile
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-)
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 07/17/2003 :  10:56:59  Show Profile
Short memory, Brett. They're Win32 FILETIME values.

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


Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  11:27:17  Show Profile
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-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  12:35:51  Show Profile
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-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  12:37:49  Show Profile
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-)
Go to Top of Page

jsmith8858
Dr. Cross Join

USA
7423 Posts

Posted - 07/17/2003 :  13:51:36  Show Profile  Visit jsmith8858's Homepage
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  13:59:23  Show Profile
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-)
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 07/17/2003 :  14:59:21  Show Profile
b/c they weren't database developers and didn't know any better, likely.

Jonathan
{0}
Go to Top of Page

Arnold Fribble
Yak-finder General

United Kingdom
1961 Posts

Posted - 07/17/2003 :  15:07:07  Show Profile
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
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  15:12:58  Show Profile
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-)
Go to Top of Page

setbasedisthetruepath
Used SQL Salesman

USA
992 Posts

Posted - 07/17/2003 :  15:25:25  Show Profile
quote:
So it'll all be done in the presentation layer.

Shout it from the hilltops ...

Jonathan
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/17/2003 :  15:43:21  Show Profile
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-)
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 07/18/2003 :  08:53:29  Show Profile
OF COURSE!

Arnold's custom should be...

drumroll please...


SQL Cronos GOD!



Brett

8-)
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000