MacLochlainns Weblog

My students have to do their group labs in Oracle and then port them individually to MySQL. Most of the conversion resolves around dates because Oracle spoils us with their simplicity in their SQL dialect.

For example, if we wanted to add 9 days to today’s system date (June 27, 2009) we can do this in Oracle:

SQL> SELECT SYSDATE + 9 FROM dual;

It prints

06-JUL-09

If we tried the equivalent in MySQL, we get a null because it treats any day value over 31 as a null. The maximum date in any month is 31, regardless of month. If you add more days than the maximum number minus your current date, you return a null. This is because adding a day never changes the month, and that yields invalid dates. In MySQL, you need to make this calculation with the adddate() or date_add() functions because they’ll increment months and years.

By way of example, if we only added four to today’s date (June 27, 2009) and formatted the output as a date, it works

mysql> SELECT str_to_date(utc_date() + 4,'%Y%m%d');

We’d get this:

+--------------------------------------+
| str_to_date(utc_date() + 4,'%Y%m%d') |
+--------------------------------------+
| 2009-06-31                           |
+--------------------------------------+

I always wanted an extra day in June. 😉

In short, there are three possible non-dates in February that format as dates, and one day in every thirty day month. I’ve a hunch this is a bug (I’ve logged a bug and they’ve verified it and moved it to triage).

The correct way to perform this calculation in MySQL is to use either the adddate() or date_add() functions. They take the same arguments. Here’s a quick example:

mysql> SELECT adddate(utc_date(),INTERVAL 9 DAY);

It yields the correct date:

+------------------------------------+
| adddate(utc_date(),INTERVAL 9 DAY) |
+------------------------------------+
| 2009-07-06                         |
+------------------------------------+

I know this answers a student email but I hope it helps a few others too.