SQL Query to fetch data after 01-01-2022

Question

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 '%2022%' I am getting the output, is there anyway I can do '%Current year%' for the like to work

Does this answer your question? [Comparing Dates in Oracle SQL](https://stackoverflow.com/questions/10178292/comparing-dates-in-oracle-sql) — user14063792468, May 26 '22 at 22:33
What data type is the column? And what does "not working at all" mean - do you get an error, or just not the result you want? — Alex Poole, May 26 '22 at 22:45
https://dba.stackexchange.com/help/minimal-reproducible-example. You should read the topic carefully and proceed with your question. — user14063792468, May 27 '22 at 15:48

score 0 · Answer 1 · answered May 26 '22 at 22:29

0

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 >=.

answered May 26 '22 at 22:29

MT0

143,790
11
59
117

I am still seeing output with 2020, 2018 etc – SSA_Tech124 May 26 '22 at 22:36
I am not sure if its because of timestamps – SSA_Tech124 May 26 '22 at 22:37
If i do like '%2022%' it works. Is there anyway I can put %'current year%' – SSA_Tech124 May 26 '22 at 22:39

score 0 · Answer 2 · answered May 27 '22 at 01:51

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');

score 0 · Answer 3 · answered May 27 '22 at 03:52

Just did some check as below, it should be able to help.

create table paaf(last_update_date timestamp);

✓

insert into paaf values(TIMESTAMP '2021-12-01 05:30:00');

1 rows affected

insert into paaf values(TIMESTAMP '2022-12-01 05:30:00');

1 rows affected

insert into paaf values(DATE '2022-01-01');

1 rows affected

select * from paaf where last_update_date >= TIMESTAMP '2022-01-01 00:00:00';

| LAST_UPDATE_DATE          |
| :------------------------ |
| 01-DEC-22 05.30.00.000000 |
| 01-JAN-22 00.00.00.000000 |

db<>fiddle here

SQL Query to fetch data after 01-01-2022

3 Answers3