Common date format function for Oracle-sql and Mys

Question

I need to write the common queries for MySQL and Oracle databases. Problem occurs when I have to put date conditions. For example: there is one field Txn_date which is in format of '20150116' in MySQL and '16-JAN-2015' in Oracle.

I use date_format(now(),'%Y%m%d') for MySQL and to_char(sysdate,'dd-MON-YYYY') for Oracle.

Is there any common function of way by which I can use the same function in both Oracle and MySQL?

I tried Txn_date in ( date_format(now(),'%Y%m%d') OR to_char(sysdate,'dd-MON-YYYY') ) but did not work because to_char() not recognized in MySQL.

Answer 1

First, in MySQL dates usually have the following format when converted implicitly - 2015-01-16 - rather than 20150116. I think you can do the following in both MySQL and Oracle (it is standard SQL) - I've checked it in Oracle (10g) and it works, and it seems to work in my fiddling with MySQL:

SELECT * FROM mytable
 WHERE mydate IN ( DATE '2015-01-16', DATE '2015-01-18' );

The string literal to be converted to DATE has to be of the form yyyy-mm-dd. Now this will work if your dates are dates and don't have a time portion. Now if your dates do have a time portion, then things become more difficult since MySQL uses the DATE() function to get the date portion, while Oracle would use TRUNC(). But you can get around that with judicious use of >= and <, e.g.:

SELECT * FROM mytable
 WHERE ( mydate >= DATE '2015-01-16' AND mydate < DATE '2015-01-17' )
    OR ( mydate >= DATE '2015-01-18' AND mydate < DATE '2015-01-19' );

Now if you want to use SYSDATE, the best thing to do would be to use the ANSI standard CURRENT_DATE or CURRENT_TIMESTAMP. These can be compared directly with no need for formatting and should work in both MySQL and Oracle. You can also do date arithmetic using INTERVAL, in which case you could try the following:

SELECT * FROM mytable
 WHERE mydate > CURRENT_DATE - INTERVAL '1' DAY;

UPDATE I've been doing some thinking about this. The query immediately above doesn't really work if you want to get all the rows that have been entered today. The difficulty is that Oracle recognizes ANSI date literals as dates (that is, with no time portion), but there isn't, as far as I know, an ANSI-standard way of converting a date/time value (which an Oracle DATE is) to a date. That said, both Oracle and MySQL support the EXTRACT() function, so you should be able to do the following to get today's records:

SELECT * FROM mytable
 WHERE EXTRACT(YEAR FROM mydate) = EXTRACT(YEAR FROM CURRENT_DATE)
   AND EXTRACT(MONTH FROM mydate) = EXTRACT(MONTH FROM CURRENT_DATE)
   AND EXTRACT(DAY FROM mydate) = EXTRACT(DAY FROM CURRENT_DATE);

Definitely unwieldy, especially if one has more than one date to consider (which I assume you do since you're using the IN operator), but should work on both platforms. See SQL Fiddle Demo here (MySQL) and here (Oracle).

Answer 2

MySQL and Oracle use different syntax for converting dates to strings - you'll have to use different queries.