Combine with month and year column kept in different columns on Oracle

Question

I keep the month and year information in different columns as numbers. I want to go back 12 months on sysdate using these columns.

The table I used below as an example , Since we are in the 5th month now, I want to get the data up to the 6th month of last year.

versiyon table :

So as a result of the query , the following result should return.

First of all, I want to query by combining the year and month columns and going back one year from the current month as a date.

Answer 1

Convert the values to strings and concatenate and then use ADD_MONTHS(SYSDATE, -12) to get last year's date (which will get the correct date regardless of whether it is a leap year or not):

SELECT *
FROM   versiyon
WHERE  TO_CHAR(year, 'fm0000') || TO_CHAR(month, 'fm00')
         >= TO_CHAR(ADD_MONTHS(SYSDATE, -12), 'YYYYMM')

db<>fiddle here

Answer 2

You can convert your year and month into an integer form of YYYYMM and compare:

SELECT * 
FROM versiyon_table 
WHERE (versiyon_table.year * 100) + versiyon_table.month > (EXTRACT(YEAR FROM SYSDATE) * 100) + EXTRACT(MONTH FROM SYSDATE)