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

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.

CodePudding user response：

select * 
from versiyon
where lpad(year, 4, '0') || lpad(month, 2, '0') >= to_number(to_char(sysdate - 365, 'yyyymm' ))

Here is a demo

CodePudding user response：

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)

CodePudding user response：

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