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

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.

CodePudding user response：

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.