4

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;
3
  • Read this: Date and Time Functions. But you need to convert data, too, not only queries. But you need to convert data/table-structure, too, not only queries. Commented Jul 12, 2012 at 13:32
  • 1
    the oracle to_char function without a second argument (the format string) formats a date value depending on settings on your database server and client that are not known to us. to what format do you want the date values be converted?
    – miracle173
    Commented Aug 9, 2012 at 5:54
  • 1
    The first statement: 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.
    – user1822
    Commented Aug 26, 2014 at 14:25

2 Answers 2

1

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);
0

regarding the first command

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

it can be converted like the following

SELECT cast(DATE_FORMAT('?', '%D-%M-%Y')as char) as date;

And for the third command

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

it can be converted like the following

select cast(dayname((DATE_FORMAT('?','%D-%M-%Y')))as char) as day;

but for the second one I could not find a very good conversion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.