Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS'
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF6'
alter session set nls_timestamp_format = 'YYYY-MM-DD HH24:MI:SS.FF6';
create table demo(demo_id number,
                  created_date date,
                  mod_date timestamp(6)
                 ); 
insert into demo (demo_id, created_date, mod_date)
values (1, sysdate, systimestamp)
1 rows affected

insert into demo (demo_id, created_date, mod_date)
values (2, sysdate, systimestamp)
1 rows affected

update demo set mod_date = systimestamp + interval '1' hour where demo_id = 2
1 rows affected

-- what you are doing
select demo_id, created_date, mod_date,
  case when created_date = mod_date then 'New' else 'Updated' end as status
from demo;


DEMO_ID
CREATED_DATE
MOD_DATE
STATUS


1
2022-08-07 00:57:44
2022-08-07 00:57:44.851512
Updated

2
2022-08-07 00:57:44
2022-08-07 01:57:44.874714
Updated


-- what is effectively happening
select demo_id, created_date, mod_date,
  cast(created_date as timestamp(6)) as created_date_as_timestamp,
  case when cast(created_date as timestamp(6)) = mod_date then 'New' else 'Updated' end as status
from demo;


DEMO_ID
CREATED_DATE
MOD_DATE
CREATED_DATE_AS_TIMESTAMP
STATUS


1
2022-08-07 00:57:44
2022-08-07 00:57:44.851512
2022-08-07 00:57:44.000000
Updated

2
2022-08-07 00:57:44
2022-08-07 01:57:44.874714
2022-08-07 00:57:44.000000
Updated


-- what you need to do
select demo_id, created_date, mod_date,
  cast(mod_date as date) as mod_date_as_date,
  case when created_date = cast(mod_date as date) then 'New' else 'Updated' end as status
from demo;


DEMO_ID
CREATED_DATE
MOD_DATE
MOD_DATE_AS_DATE
STATUS


1
2022-08-07 00:57:44
2022-08-07 00:57:44.851512
2022-08-07 00:57:44
New

2
2022-08-07 00:57:44
2022-08-07 01:57:44.874714
2022-08-07 01:57:44
Updated

DEMO_ID	CREATED_DATE	MOD_DATE	STATUS
1	2022-08-07 00:57:44	2022-08-07 00:57:44.851512	Updated
2	2022-08-07 00:57:44	2022-08-07 01:57:44.874714	Updated

DEMO_ID	CREATED_DATE	MOD_DATE	CREATED_DATE_AS_TIMESTAMP	STATUS
1	2022-08-07 00:57:44	2022-08-07 00:57:44.851512	2022-08-07 00:57:44.000000	Updated
2	2022-08-07 00:57:44	2022-08-07 01:57:44.874714	2022-08-07 00:57:44.000000	Updated