Shifting Dates by a Known Amount
Problem
You want to shift a given date by a given amount to compute the resulting date.
Solution
Use DATE_ADD( )
or DATE_SUB( )
.
Discussion
If you have a reference date and want to calculate another date from
it that differs by a known interval, the problem generally can be
solved by basic date arithmetic using DATE_ADD( )
and
DATE_SUB( )
. Some examples of this kind of question
include finding anniversary dates, determining expiration dates, or
finding records that satisfy “this date in
history” queries. This section illustrates a couple
of applications for date shifting.
Calculating Anniversary Dates
Suppose you’re getting married on August 6, 2003, and you don’t want to wait a year for your first anniversary to show your devotion to your sweetheart. Instead, you want to get her special gifts on your 1 week, 1 month, 3 month, and 6 month anniversaries. To calculate those dates, shift your anniversary date forward by the desired intervals, as follows:
mysql>SET @d = '2003-08-06';
mysql>SELECT @d AS 'start date',
->DATE_ADD(@d,INTERVAL 7 DAY) AS '1 week',
->DATE_ADD(@d,INTERVAL 1 MONTH) AS '1 month',
->DATE_ADD(@d,INTERVAL 3 MONTH) AS '3 months',
->DATE_ADD(@d,INTERVAL 6 MONTH) AS '6 months';
+------------+------------+------------+------------+------------+ | start date | 1 week | 1 month | 3 months | 6 months | +------------+------------+------------+------------+------------+ | 2003-08-06 | 2003-08-13 | 2003-09-06 | 2003-11-06 | 2004-02-06 | +------------+------------+------------+------------+------------+ ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.