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
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
