Bug #16690 | Allow date calculations on incomplete dates (like YYYY-MM-00) | ||
---|---|---|---|
Submitted: | 20 Jan 2006 22:42 | Modified: | 27 Oct 2008 22:44 |
Reporter: | Peter Thomassen | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Data Types | Severity: | S4 (Feature request) |
Version: | All (?) | OS: | Any |
Assigned to: | CPU Architecture: | Any |
[20 Jan 2006 22:42]
Peter Thomassen
[18 Jul 2006 18:54]
Peter Thomassen
Any progress on this?
[18 Jul 2006 23:10]
Peter Thomassen
I just noticed that CAST('YYYY-MM-00' AS DATE) also works, but that it isn't possible to wrap this through a stored function like _d('YYYY-MM-00'). It seems that another type conversion takes place, although RETURNS DATE is defined.
[19 Jul 2006 2:03]
Jon Stephens
This behaviour also seems a bit odd: mysql> SELECT STR_TO_DATE('2006-07-00', '%Y-%m') = '2006-07-00' AS a; +---+ | a | +---+ | 1 | +---+ 1 row in set, 1 warning (0.00 sec) mysql> SHOW WARNINGS; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect date value: '2006-07-00' | +---------+------+----------------------------------------------+ 1 row in set (0.00 sec) If two values are reported to be equal, then they should be treated the same way. If they're not handled the same way, they ought to be reported as unequal (and the first query should yield 0, not 1). This should be considered a bug IMO, and not a feature request.
[19 Jul 2006 3:45]
Jon Stephens
I verified this behaviour in 5.1-bk-20060704.
[31 Oct 2006 10:33]
Ramil Kalimullin
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/13798 ChangeSet@1.2302, 2006-10-17 16:30:49+05:00, ramil@mysql.com +3 -0 Fix for bug #22229: Bug in DATE_ADD() From the manual: date arithmetic operations require complete dates and do not work with incomplete dates such as '2006-07-00' or badly malformed dates.
[31 Oct 2006 10:44]
Peter Thomassen
Unfortunately, I don't have the coding skills to understand that patch. Does it mean that "YYYY-MM-00" + INTERVAL 1 MONTH will work, if the patch is applied? Thanks!
[31 Oct 2006 10:54]
Ramil Kalimullin
No. It fixes the problem described by Jon Stephens: "If two values are reported to be equal, then they should be treated the same way. If they're not handled the same way, they ought to be reported as unequal (and the first query should yield 0, not 1). This should be considered a bug IMO, and not a feature request."
[31 Oct 2006 11:44]
Peter Thomassen
Hm. Actually, this is not what this bug report is about; Jon mentions another issue. I think the bug shouldn't be marked as "Patch pending" (and later supposedly "Fixed") when another (related) bug is fixed. I reported this bug to request the implementation of date calculations.
[1 Nov 2006 1:46]
Timothy Smith
Peter, Thanks for the comment. I agree with you, this feature request should not be tossed away just because it led to a (narrower in scope) bug report. As the bug process is already being handled in bug #22229, I am returning this bug to a Feature request. Thank you, Timothy
[26 Nov 2006 9:08]
Jon Stephens
Not sure why this was assigned to me, since this doesn't appear to be a Docs issue. Nor am I sure who should look at it and get it into the proper queue. Elliot, can you please route this to whomever/wherever it needs to go? Thanks!
[27 Oct 2008 22:44]
Peter Thomassen
In the original posting, I wrote: | Currently, MySQL returns NULL when executing the above query; I use | SELECT STR_TO_DATE('2005-05-00', '%Y-%m') + INTERVAL 1 MONTH AS date; | as a workaround I just noticed that this doesn't work anymore! This makes things harder for me, as I do not know how I can do my invoicing calculations based on stored procedures. That's bad; if I don't find another workaround, I have to rewrite it all in the application language. The above statement now just returns NULL. I don't know which version made this workaround disfunctional. Is it that hard to implement this feature? (As I said, I would do it myself if I could ...)
[21 Nov 2015 1:10]
Tim Hardin
I am seeing a similar issue when I calculate the difference, in months, between two dates, using TIMESTAMPDIFF. I first convert the dates, formatted as abbreviated month and year, using STR_TO_DATE, then I use TIMESTAMPDIFF. - The query (partial) I want to run is: SELECT TIMESTAMPDIFF(MONTH, STR_TO_DATE(FirstPayment,"%b-%Y"), STR_TO_DATE(LastPayment,"%b-%Y")) NULL - STR_TO_DATE does convert, but the date is incomplete. SELECT STR_TO_DATE("Jan-2000", "%b-%Y") 2000-01-00 - DATE_ADD doesn't work either, on incomplete dates: SELECT DATE_ADD('2000-01-00', INTERVAL 1 DAY) NULL SELECT DATE_ADD('2000-01-01', INTERVAL 1 DAY) 2000-01-02