| Author |
 Topic  |
|
|
X002548
Not Just a Number
15586 Posts |
 Posted - 07/17/2003 : 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
USA
478 Posts |
Posted - 07/17/2003 : 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 - 07/17/2003 : 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
United Kingdom
1961 Posts |
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 07/17/2003 : 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 - 07/17/2003 : 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 - 07/17/2003 : 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
USA
7423 Posts |
Posted - 07/17/2003 : 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 - 07/17/2003 : 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
USA
992 Posts |
Posted - 07/17/2003 : 14:59:21
|
b/c they weren't database developers and didn't know any better, likely.
Jonathan
{0} |
|
|
|
Arnold Fribble
Yak-finder General
United Kingdom
1961 Posts |
Posted - 07/17/2003 : 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 - 07/17/2003 : 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
USA
992 Posts |
Posted - 07/17/2003 : 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 - 07/17/2003 : 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 - 07/18/2003 : 08:53:29
|
OF COURSE!
Arnold's custom should be...
drumroll please...
SQL Cronos GOD!
Brett
8-) |
|
|
| |
Topic |
|
Date as Binary(8)
Forum Locked
