Question: Date field

[B]

Using SQL2000, to give a little history. Original data stored on DB2, and
transferred on MySQL into one table with 50 fields and more than 500,00+
records. This then gets transferred to sqlserver via DTS, also in the same
structure as MySQL.

The exception of one field, where shipping_date was assigned as "decimal"
data type on DB2 and MySQL.
On sqlserver, I changed the datatype to "datetime", it gets converted
through DTS.

The reason why I made the change is because it is quicker to retrieve the
data and for assigning indexes. Am I wrong with this justification? Now
upper mgnt is questioning me about it.

What would be the disadvantage if I were to leave "shipping_date" as
decimal? data looks like yyyymmdd (20040201), my concern since the table is
not normalized and and it takes forever to drop and re-create the indexes,
that I have totally abandoned the indexes.

Please advise, TIA!
Bob

[John Bell]

Hi

It sounds like you have missed the opportunity to use a normalise the
structure when you transfer it into SQL Server!

If you are not displaying the shipping date or display it as is, then you
are probably better leaving it as it is, or converting to integer. An
integer will be 4 bytes, decimal(8,0) will be 5 bytes and datetime 2*4
bytes. Whatever you do, indexes will almost certainly significantly improve
the performance.

John

"B" <no_spam@no_spam.com> wrote in message
news:s8********************@rcn.net...

Using SQL2000, to give a little history. Original data stored on DB2, and
transferred on MySQL into one table with 50 fields and more than 500,00+
records. This then gets transferred to sqlserver via DTS, also in the same
structure as MySQL.

The exception of one field, where shipping_date was assigned as "decimal"
data type on DB2 and MySQL.
On sqlserver, I changed the datatype to "datetime", it gets converted
through DTS.

The reason why I made the change is because it is quicker to retrieve the
data and for assigning indexes. Am I wrong with this justification? Now
upper mgnt is questioning me about it.

What would be the disadvantage if I were to leave "shipping_date" as
decimal? data looks like yyyymmdd (20040201), my concern since the table
is
not normalized and and it takes forever to drop and re-create the indexes,
that I have totally abandoned the indexes.

Please advise, TIA!
Bob

[Hugo Kornelis]

On Sun, 6 Feb 2005 10:11:12 -0500, B wrote:

(snip)

The exception of one field, where shipping_date was assigned as "decimal"
data type on DB2 and MySQL.
On sqlserver, I changed the datatype to "datetime", it gets converted
through DTS.

(snip)

Hi Bob,

Seems like an excellent choice to me. If you want to store dates, use a
datetime datatype - any other choice is silly and begging for trouble.

Do you realize how complicated your CHECK constraint needs to get to make
sure that only valid dates are entered if you store the date as numeric?

Do you realize how hard is is to calculate the number of days from Dec 28
2004 to March 17 2005 if you store the date as numeric?

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

[Erland Sommarskog]

B (no_spam@no_spam.com) writes:

The exception of one field, where shipping_date was assigned as "decimal"
data type on DB2 and MySQL.
On sqlserver, I changed the datatype to "datetime", it gets converted
through DTS.
Converted to what? I don't know DTS, but I doubt that it would convert
the number 20040201 to the date 2004-02-01 automatically. I would expect
the result to be 20040201 days from 1900-01-01, which is out of range for
datetime.

Since you know more DTS than I do, maybe your were smart enough to write
some transformation thing to get it right. But I wanted to point this
out in case.
The reason why I made the change is because it is quicker to retrieve the
data and for assigning indexes. Am I wrong with this justification? Now
upper mgnt is questioning me about it.
Well, if some program that was reading the database broke because of
your change, maybe you were wrong.

Generally, though, I would recommend using datetime for dates, even if,
as John says, the datetime value is actually twice the sice of an int.
But, as Hugo says, computation and validation of the numeric values is
a mess.
data looks like yyyymmdd (20040201), my concern since the table is
not normalized and and it takes forever to drop and re-create the indexes,
that I have totally abandoned the indexes.

If you drop the indexes, then the queries against the tables will
take even longer time. But you may have indexes that does not really
serve any purposes.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp