MySQL Bugs: #13456: Date calculations fail on dates like YYYY-MM-00

Bug #13456	Date calculations fail on dates like YYYY-MM-00
Submitted:	24 Sep 2005 9:39	Modified:	20 Jan 2006 22:42
Reporter:	Peter Thomassen	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	4.1.11	OS:	Linux (Linux)
Assigned to:	Paul DuBois	CPU Architecture:	Any

Description:
When doing date calculations like the following, MySQL returns NULL if the date which is operated on is of the form YYYY-MM-00.

YYYY-MM-00 may be used to describe only months (for invoicing etc.), as said in http://dev.mysql.com/doc/mysql/en/datetime.html: "However, as of MySQL 3.23, you can explicitly specify a value of zero to represent missing month or day parts. For example, you can use '990300' to insert the value '1999-03-00'."

So it should be possible to operate correctly on these values.

BTW, EXTRACT(YEAR_MONTH FROM '2005-03-00') correctly returns 200503.

How to repeat:
SELECT '2005-03-01' + INTERVAL 1 MONTH AS date;
correctly returns 2005-04-01.

SELECT '2005-03-00' + INTERVAL 1 MONTH AS date;
does not return 2005-04-00, but NULL.

This looks to me as a documentation issue for to explain that the use in date
calculation which involves INTERVAL and an incomplete date, like your sample
should be handled as invalid datetime. 

mysql> SELECT '2005-03-00' + INTERVAL 1 MONTH AS date;
+------+
| date |
+------+
| NULL |
+------+
1 row in set, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1292 | Truncated incorrect datetime value: '2005-03-00' |
+---------+------+--------------------------------------------------+
1 row in set (0.00 sec)

http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html

"The return value ranges in the following function descriptions apply for complete dates.
If a date is a “zero” value or an incomplete date such as '2001-11-00', functions that
extract a part of a date may return 0. For example, DAYOFMONTH('2001-11-00') returns 0."

mysql> select DAYOFMONTH('2001-11-00');
+--------------------------+
| DAYOFMONTH('2001-11-00') |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

mysql> SELECT EXTRACT(DAY FROM '2005-03-00');
+--------------------------------+
| EXTRACT(DAY FROM '2005-03-00') |
+--------------------------------+
|                              0 |
+--------------------------------+
1 row in set (0.00 sec)

I disagree. My application produces PDF invoices in a six month cycle. In this and in other situations, it only makes sense to store the last already invoiced month or the month of contract conclusion; a more precise specification by adding a concret day may lead to the wrong assumption that the accounting happens day-wise.

If it were possible to correctly operate on incomplete dates, which -- as I already mentioned -- are correct in the sense of a valid value, the application could easily distinguish between monthly and daily accounting and use month or day calculations, resp.

As a workaround, instead of
	SELECT '2005-05-00'+ INTERVAL 1 MONTH AS date;

I am using
	SELECT STR_TO_DATE('2005-05-00', '%Y-%m') + INTERVAL 1 MONTH AS date;

I don't know, why this works, but it does. It seems to me that the only difference is the internal representation of the incomplete date, which in the first case is a string and in the second case is a date value. Please note that the second statement really returns '2005-06-00'.

Why can't the need of this conversion be eliminated?

Please, could you dwell on what I mentioned before and explain, why this obvious malfunction cannot be fixed? Of course, you can always just note bugs in the docs and tell people not to provoke errors caused thereby, but this looks quite unprofessional and runs straight contrary to the idea of open source.

No offense meant!

Thank you for your bug report. This issue has been addressed in the
documentation. The updated documentation will appear on our website
shortly, and will be included in the next release of the relevant
product(s).

Additional info:

The actual behavior is that date arithmetic requires complete
dates, so I will make it clearer in the date section of the manual
that this is the case.

The behavior described in this report could be entered as a feature
request if desired.

Yes, I wish to make this a feature request. Shall I file a new bug, or just change the severity of this one?

Thanks,
Peter

Please file a new request, thanks.  You can reference this bug report
as the background for the request.

Done, see bug #16690.