Your second query can be converted to MySQL in two ways.
First one is simple query, that adds interval
of days to compared date, depending on which dayofweek
it is. It's not the most elegant solution, and different days of week require different values (or selects).
Code below uses CURDATE
as a starting value, query for each column can be used separately:
SELECT
DATE(CURDATE()) AS now_date,
DATE_ADD(DATE(CURDATE()), INTERVAL (9 - IF(DAYOFWEEK(CURDATE()) < 2, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_monday,
DATE_ADD(DATE(CURDATE()), INTERVAL (10 - IF(DAYOFWEEK(CURDATE()) < 3, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_tuesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (11 - IF(DAYOFWEEK(CURDATE()) < 4, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_wednesday,
DATE_ADD(DATE(CURDATE()), INTERVAL (12 - IF(DAYOFWEEK(CURDATE()) < 5, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_thursday,
DATE_ADD(DATE(CURDATE()), INTERVAL (13 - IF(DAYOFWEEK(CURDATE()) < 6, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_friday,
DATE_ADD(DATE(CURDATE()), INTERVAL (14 - IF(DAYOFWEEK(CURDATE()) < 7, (7 + DAYOFWEEK(CURDATE())), DAYOFWEEK(CURDATE()))) DAY) AS next_saturday,
DATE_ADD(DATE(CURDATE()), INTERVAL (8 - DAYOFWEEK(CURDATE())) DAY) AS next_sunday;
Code found and copied from old GizmoLA post's comment. Similar one, just for Sunday, was explained on StackOverflow.
Second one is more universal, but requires creating function. In this case it's code could look like this one, found on SQLines post:
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;
Usage (show next sunday):
SELECT NEXT_DAY(NOW(), 'Sun');
You can also use NEXT_DAY and SUBDATE functions to get the previous date:
SELECT SUBDATE(NEXT_DAY(NOW(), 'Sun'), 7);
to_char(to_date(?,'DD-MM-YY'))
doesn't make sense in Oracle. It converts a string in the format'DD-MM-YY'
to a "real" date, which is then converted back to some string representation, formatted according to the client's NLS settings. This is a bug waiting to happen, you shouldn't do it like that in Oracle.