Jochem van Dieten wrote:
> David Blomstrom wrote:
>
>>
>> Suppose you have a list of dates in the proper format,
>> like this:
>>
>> 2004-02-04
>> 2003-11-02
>>
>> and you encounter a date with only the month and year,
>> like May 2002. How would you insert that
>
>
> Not. Prompt the user for a full date.
>
> Jochem
That is not strictly true. You can use 00 if there is not a day, that is,
insert May 2002 as '2002-05-00'. This can be useful for historical data
where you know the month but not the day. If you do this, however, you must
account for it in any code which uses the date. That is, as soon as you
allow this, you cannot assume any date has a valid day part.
MySQL will also let you insert a date of '2002-00-00'. Again, this might
prove useful for data where sometimes only the year is known, but you would
have to code with the assumption that dates might be just years.
Note that mysql does not consider dates with 00s to be valid dates, which
affects some date functions but not others. For example, you can pull out
the day, month, and year parts of partial dates
SELECT YEAR('2002-05-00'), MONTH('2002-05-00'), DAYOFMONTH('2002-05-00');
+--------------------+---------------------+--------------------------+
| YEAR('2002-05-00') | MONTH('2002-05-00') | DAYOFMONTH('2002-05-00') |
+--------------------+---------------------+--------------------------+
| 2002 | 5 | 0 |
+--------------------+---------------------+--------------------------+
1 row in set (0.00 sec)
SELECT YEAR('2002-00-00'), MONTH('2002-00-00'), DAYOFMONTH('2002-00-00');
+--------------------+---------------------+--------------------------+
| YEAR('2002-00-00') | MONTH('2002-00-00') | DAYOFMONTH('2002-00-00') |
+--------------------+---------------------+--------------------------+
| 2002 | 0 | 0 |
+--------------------+---------------------+--------------------------+
1 row in set (0.00 sec)
but you can't add/subtract an interval to/from a partial date
SELECT '2002-05-04' + INTERVAL 1 DAY AS full_date,
'2002-05-00' + INTERVAL 1 DAY AS no_day,
'2002-00-00' + INTERVAL 1 DAY AS no_month;
+------------+--------+----------+
| full_date | no_day | no_month |
+------------+--------+----------+
| 2002-05-05 | NULL | NULL |
+------------+--------+----------+
1 row in set (0.00 sec)
One should consider carefully whether allowing partial dates will be worth
the hassle.
Michael
