How to get current month records from RDBMS without using DATE functions like CURDATE,MONTH,YEAR

Question

I want to write sql compatibility query for mysql nad sqlite3. in mysql i wrote a query to get current month records as

so_date is date field(YYYY-MM-DD)

select so_no,so_date from so_master where MONTH(CURDATE()) = MONTH(so_date) AND YEAR(CURDATE()) = YEAR(so_date);

this is not working in sqlite3. in sqlite I can write like

 select so_no,so_date from so_master where strftime('%Y-%m', so_date) = strftime('%Y-%m', 'now');

but my application cannot work if i change the database like mysql to sqlite3 or sqlite3 to mysql

so i want my query to work in both the databases with one query.

Answer 1

You can do it if you treat the dates as strings with the the function SUBSTR() and CURRENT_DATE which is common in both MySql and SQLite:

SELECT so_no, so_date 
FROM so_master 
WHERE SUBSTR(so_date, 6, 2) = SUBSTR(CURRENT_DATE, 6, 2)
  AND SUBSTR(so_date, 1, 4) = SUBSTR(CURRENT_DATE, 1, 4);

Or, simpler:

SELECT so_no, so_date 
FROM so_master 
WHERE SUBSTR(so_date, 1, 7) = SUBSTR(CURRENT_DATE, 1, 7);

Demo for SQLite and for MySql.