I have a DAY_DATE column that contains values such as ’11-2-2021′. I want to extract the rows where the value is within the last year or higher. Meaning that the current date is 11-3-2022, which means that I want to get all rows where the date is 11-3-2021 or sooner.
I tried the following:
SELECT * FROM TABLE_A
WHERE 1=1
AND TO_CHAR(DAG_DATUM_VERZONDEN, 'YYYYIW') >= TO_CHAR(SYSDATE, 'IYYYIW') - 52
However, this isn’t working as you cannot always substract 52 weeks. E.g. when you are in the 10th week of 2022, then you can only substract 2022 (it will not include anything from 2021, as it does not include a change in years). I again tried to do something else:
SELECT * FROM TABLE_A
WHERE 1=1
AND TO_CHAR(DAG_DATUM_VERZONDEN, 'YYYYMM') >= to_char(trunc(add_months(SYSDATE,-12),'YEAR'),'YYYYMM')
However, this return everything from 2021, and not necessarly the rows that are higher than 11-3-2021. ANyone else that knows the answer to this problem? Thanks for any help in advance.
>Solution :
If DAG_DATUM_VERZONDEN column’s datatype is DATE (should be; I hope it isn’t VARCHAR2), then
SELECT *
FROM table_a
WHERE dag_datum_verzonden >= ADD_MONTHS (TRUNC (SYSDATE), -12)
If you do store dates as strings (once again, bad idea), then first convert it (the string) to a valid date value:
WHERE TO_DATE(dag_datum_verzonden, 'dd-mm-yyyy') >= ADD_MONTHS (TRUNC (SYSDATE), -12)