Mysql – Convert Oracle Query for Date to MySQL queries

date formatmysql-5.5

Need to convert the following oracle queries to MySQL queries.

1)

select to_char(to_date(?,'DD-MM-YY')) 
from dual;

2)

select to_char(next_day(to_date(?,'DD-MM-YY'),'FRIDAY')) 
from dual;

3)

select to_char(to_date(?,'DD-MM-YY'),'DAY') 
from dual;

Best Answer

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);
Related Question