Hi!

daniel> According to the MySQL manual (MySQL 3.22.32, chapter 7.3.6), MySQL can daniel> process incomplete dates. I would like to make use of this capability
for a daniel> family database. For some ancestors, I only know the year of certain
events daniel> (e.g. birth), but neither month nor day. When trying to use incomplete daniel> dates, I came across the problem that daniel> a) incomplete dates can be set only in specific formats. daniel> b) the YEAR()-function does not seem to work on incomplete dates. daniel> If the YEAR()-function doesn't work, I can't search on incomplete dates, daniel> making them fairly useless.

daniel> # daniel> # The following code demonstrates the problem. daniel> # It works as follows: daniel> # a) demo table is being created (the date-field is called Geburt) daniel> # b) a record is inserted (Geburt=NULL) daniel> # c) four different dates are UPDATEd, and their values and years
SELECTed. daniel> # All four YEAR functions should yield 1640, but they don't. daniel> # The last three dates should yield '1640-00-00', but only the last
does. daniel> #

daniel> # daniel> # daniel> # Create Table< daniel> #

daniel> CREATE TABLE BUGDEMO ( daniel> Vorname varchar(50) DEFAULT '' NOT NULL, daniel> Name varchar(50), daniel> Geburt date, daniel> ID mediumint(9) DEFAULT '0' NOT NULL, daniel> PRIMARY KEY (ID) daniel> );

daniel> # daniel> # insert record daniel> # daniel> INSERT INTO BUGDEMO VALUES daniel> ('Christian', 'Hennecken', NULL, 482);

daniel> # daniel> # test: daniel> # a) '1640-01-01' => date OK, YEAR() OK daniel> # b) '1640-00-00' => bug: zero date, YEAR() is NULL daniel> # c) '16400000' => bug: zero date, YEAR() is NULL daniel> # d) 16400000 => date OK, bug: YEAR() is NULL

daniel> UPDATE BUGDEMO SET Geburt = '1640-01-01' WHERE ID=482; daniel> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO;

daniel> UPDATE BUGDEMO SET Geburt = '1640-00-00' WHERE ID=482; daniel> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO;

daniel> UPDATE BUGDEMO SET Geburt = '16400000' WHERE ID=482; daniel> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO;

daniel> UPDATE BUGDEMO SET Geburt = 16400000 WHERE ID=482; daniel> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO;

daniel> DROP TABLE BUGDEMO;

Note that it's only MySQL 3.23 that supports dates where month or day is 0 !

Here is the output from the above selects from MySQL 3.23.18:

mysql> INSERT INTO BUGDEMO VALUES -> ('Christian', 'Hennecken', NULL, 482); Query OK, 1 row affected (0.02 sec)

mysql> mysql> UPDATE BUGDEMO SET Geburt = '1640-01-01' WHERE ID=482; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO; +-----+------------+--------------+ | ID | Geburt | YEAR(Geburt) | +-----+------------+--------------+ | 482 | 1640-01-01 | 1640 | +-----+------------+--------------+ 1 row in set (0.01 sec)

mysql> UPDATE BUGDEMO SET Geburt = '16400000' WHERE ID=482; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO; +-----+------------+--------------+ | ID | Geburt | YEAR(Geburt) | +-----+------------+--------------+ | 482 | 1640-00-00 | 1640 | +-----+------------+--------------+ 1 row in set (0.05 sec)

mysql> mysql> UPDATE BUGDEMO SET Geburt = 16400000 WHERE ID=482; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

mysql> SELECT ID, Geburt, YEAR(Geburt) FROM BUGDEMO; +-----+------------+--------------+ | ID | Geburt | YEAR(Geburt) | +-----+------------+--------------+ | 482 | 1640-00-00 | 1640 | +-----+------------+--------------+ 1 row in set (0.00 sec)

Regards, Monty