SQL Query to fetch data after 01-01-2022-CodePudding

I have a date coming in the format 2022-05-26T13:50:44.000Z, 2018-10-05T18:14:00.000Z. I want the date to be picked after 2022 only from this column. But when I am using the below query it is not working at all.

 to_date(TO_CHAR(TRUNC(Paaf.last_update_date),'MM-DD-YYYY'),'MM-DD-YYYY') >= TO_DATE('01-01-2022','MM-DD-YYYY')

How to compare the date so that it only the dates after 01-01-2022 are picked

If i do Paaf.last_update_date like ' 22%' I am getting the output, is there anyway I can do '%Current year%' for the like to work

CodePudding user response：

You don't need to truncate then convert to a string and convert back to a date.

Just compare the DATE column to a date literal:

SELECT *
FROM   table_name paaf
WHERE  paaf.last_update_date >= DATE '2022-01-01'

If you want dates after 2022-01-01 00:00:00 then use > rather than >=.

CodePudding user response：

Assuming the date part format to be with timezone. Using cast and to_utc_timestamp_tz oracle functions in following manner.

with x as ( select CAST(to_utc_timestamp_tz('2022-05-26T13:50:44.000Z') AS DATE) as sample_year from dual ) select * from x where sample_year > TO_DATE('2021-01-01','YYYY-MM-DD');