On Wed, 2005-09-14 at 15:49 +1200, Brent Wood wrote:
>
> Sanitizing is one thing, inventing data to fit an incomplete value into a
> date datatype is not good practice.
Choose another datatype, or make a new one, or split the date into
columns. The type of your data is not a timestamp nor a date.
In the first place, 0 is not NULL. So, even 1980-01-00 would be
different from 1980-01-NULL. For example, assuming 1980-01-00 is defined
to have some meaning, (1980-01-00 < 1980-01-02) is likely to be true,
but (1980-01-NULL < 1980-01-02) definitely is not.
You're just asking if there's a way to store a number of which the lower
bits are ignored (considered NULL). Obviously, no, you need a different
datatype or a different arrangement.
Note: the string 1980-01-00 just *looks* like a possible value, but
definitely it's not: there's simply no "space" (or time) between
1979-12-31 and 1980-01-01. It's much like trying to store sqrt(-1) into
a real. I hardly can imagine how MySQL manages to store that (the
1980-01-00, I mean).
.TM.
--
____/ ____/ /
/ / / Marco Colombo
___/ ___ / / Technical Manager
/ / / ESI s.r.l.
_____/ _____/ _/ Colombo@ESI.it