Date Arithmetic With MySQL - A Short Interval
(Page 6 of 8 )
The PERIOD_DIFF() function is primarily used to calculate the number of months between two dates - as illustrated in the following example, which calculates the number of months between December 2002 and December 2003.
mysql> SELECT PERIOD_DIFF(200312, 200212);
+-----------------------------+
| PERIOD_DIFF(200312, 200212) |
+-----------------------------+
| 12 |
+-----------------------------+
1 row in set (0.00 sec)
The values provided to the PERIOD_DIFF() function must be in the form YYYYMM or YYMM - the following statement is equivalent to the one above:
mysql> SELECT PERIOD_DIFF(0312, 0212);
+-------------------------+
| PERIOD_DIFF(0312, 0212) |
+-------------------------+
| 12 |
+-------------------------+
1 row in set (0.00 sec)
A corollary to the PERIOD_DIFF() function is the PERIOD_ADD() function, which adds a specified number of months to a date and displays the result. The first argument to PERIOD_ADD() is the start date, the second is the number of months to be added to it. Consider the following example, which adds 3 months to January 2003:
mysql> SELECT PERIOD_ADD(200301, 3);
+-----------------------+
| PERIOD_ADD(200301, 3) |
+-----------------------+
| 200304 |
+-----------------------+
1 row in set (0.02 sec)
You can even use the PERIOD_ADD() function to perform a subtraction operation, by specifying a negative integer as the second argument to PERIOD_ADD(). Consider the following example, which illustrates by subtracting 5 months from May 2003.
mysql> SELECT PERIOD_ADD(200305, -5);
+------------------------+
| PERIOD_ADD(200305, -5) |
+------------------------+
| 200212 |
+------------------------+
1 row in set (0.00 sec)
Next: Lather, Rinse, Repeat >>
More MySQL Articles
More By icarus, (c) Melonfire
/ 34 
