What is the date of the next Sunday? In Oracle, you can use NEXT_DAY function to answer this question. In MySQL, you can use an user-defined function.
In Oracle, NEXT_DAY function returns the date of the first day specified by its name later than the specified start day:
Oracle:
-- Get the date of the next Sunday (current date is Thursday, November 22, 2012) SELECT NEXT_DAY(SYSDATE, 'Sun') FROM dual; # 25-NOV-2012
You can also use NEXT_DAY to get the previous date by subtracting 7:
-- Get the date of the previous Sunday (current date is Thursday, November 22, 2012) SELECT NEXT_DAY(SYSDATE, 'Sun') - 7 FROM dual; # 18-NOV-2012
You can use the following user-defined function to implement NEXT_DAY in MySQL:
MySQL:
DELIMITER // CREATE FUNCTION next_day(start_date DATETIME, weekday CHAR(20)) RETURNS DATETIME BEGIN DECLARE start DATETIME; DECLARE i INT; // Select the next date SET start = ADDDATE(start_date, 1); SET i = 1; days: LOOP -- Compare the day names IF SUBSTR(DAYNAME(start), 1, 3) = SUBSTR(weekday, 1, 3) THEN LEAVE days; END IF; // Select the next date SET start = ADDDATE(start, 1); SET i = i + 1; -- Not valid weekday specified IF i > 7 THEN SET start = NULL; LEAVE days; END IF; END LOOP days; RETURN start; END; // DELIMITER ;
After you created user-defined function NEXT_DAY, you can query the next and previous days as follows:
MySQL:
-- Get the date of the next Sunday (current date is Thursday, November 22, 2012) SELECT NEXT_DAY(NOW(), 'Sun'); # 2012-11-25 17:20:46
You can also use NEXT_DAY and SUBDATE functions to get the previous date:
-- Get the date of the previous Sunday (current date is Thursday, November 22, 2012) SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7); # 2012-11-18 17:22:50
SQLines offers services and tools to help you migrate databases and applications. For more information, please contact us at support@sqlines.com.
Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - November 2012.