Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table table_name (user_id, modified_datetime, flag) as
select 123, to_date('2021/10/01 13:00:00', 'YYYY/MM/DD HH24:MI:SS'), 'A' from dual
union all
select 123, to_date('2021/10/01 12:30:00', 'YYYY/MM/DD HH24:MI:SS'), 'B' from dual
2 rows affected

select value from nls_session_parameters where parameter = 'NLS_DATE_FORMAT';


VALUE


DD-MON-RR


-- implicit (NLS) and explicit formatting of the dates
select modified_datetime, to_char(modified_datetime, 'yyyy/mm/dd hh24:mi:ss')
from table_name;


MODIFIED_DATETIME
TO_CHAR(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')


01-OCT-21
2021/10/01 13:00:00

01-OCT-21
2021/10/01 12:30:00


-- how implicit (NLS) string conmverts back to a date - with midnight time
select to_date('01-OCT-21'), to_char(to_date('01-OCT-21'), 'yyyy/mm/dd hh24:mi:ss')
from dual;


TO_DATE('01-OCT-21')
TO_CHAR(TO_DATE('01-OCT-21'),'YYYY/MM/DDHH24:MI:SS')


01-OCT-21
2021/10/01 00:00:00


-- how your dates appear after implicit conversion to string and backto dates
select modified_datetime,
  to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'),
  to_char(to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')
from table_name;


MODIFIED_DATETIME
TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')
TO_CHAR(TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS'),'YYYY/MM/DDHH24:MI:SS')


01-OCT-21
21-OCT-01
0001/10/21 00:00:00

01-OCT-21
21-OCT-01
0001/10/21 00:00:00


-- your query using implicit conversion
select
a.user_id as user_id,
(to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss') - to_date(b.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')) * 60 * 24 as minutes
from 
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;


USER_ID
MINUTES


123
0


-- set session NLS to match your assumptions - do not rely on this
alter session set nls_date_format = 'yyyy/mm/dd/hh24:mi:ss';
-- implicit (NLS) and explicit formatting of the dates
select modified_datetime, to_char(modified_datetime, 'yyyy/mm/dd hh24:mi:ss')
from table_name;


MODIFIED_DATETIME
TO_CHAR(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')


2021/10/01/13:00:00
2021/10/01 13:00:00

2021/10/01/12:30:00
2021/10/01 12:30:00


-- how implicit (NLS) string conmverts back to a date - with midnight time
select to_date('01-OCT-21'), to_char(to_date('01-OCT-21'), 'yyyy/mm/dd hh24:mi:ss')
from dual;


TO_DATE('01-OCT-21')
TO_CHAR(TO_DATE('01-OCT-21'),'YYYY/MM/DDHH24:MI:SS')


0001/10/21/00:00:00
0001/10/21 00:00:00


-- how your dates appear after implicit conversion to string and backto dates
select modified_datetime,
  to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'),
  to_char(to_date(modified_datetime, 'yyyy/mm/dd hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss')
from table_name;


MODIFIED_DATETIME
TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')
TO_CHAR(TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS'),'YYYY/MM/DDHH24:MI:SS')


2021/10/01/13:00:00
2021/10/01/13:00:00
2021/10/01 13:00:00

2021/10/01/12:30:00
2021/10/01/12:30:00
2021/10/01 12:30:00


-- your query using implicit conversion (but matching NLS)
select
a.user_id as user_id,
(to_date(a.modified_datetime, 'yyyy/mm/dd hh24:mi:ss') - to_date(b.modified_datetime, 'yyyy/mm/dd hh24:mi:ss')) * 60 * 24 as minutes
from 
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;


USER_ID
MINUTES


123
30


-- your query modified to avoid conversions (matching NLS, but not used)
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from 
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id
;


USER_ID
MINUTES


123
30


-- revert NLS setting to show it works with either, since it isn't used
alter session set nls_date_format = 'DD-MON-RR';
-- your query modified to avoid conversions (re-run with mismatched NLS, but not used, so still OK)
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from 
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a,
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
where a.user_id = b.user_id;


USER_ID
MINUTES


123
30


-- same query with ANSI joins
select
a.user_id as user_id,
(a.modified_datetime - b.modified_datetime) * 60 * 24 as minutes
from 
(select user_id, modified_datetime from table_name where /* some_conditions */ flag = 'A') a
join
(select user_id, modified_datetime from table_name where /* other_conditions */ flag = 'B') b
on a.user_id = b.user_id;


USER_ID
MINUTES


123
30
MODIFIED_DATETIME	TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')	TO_CHAR(TO_DATE(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS'),'YYYY/MM/DDHH24:MI:SS')
01-OCT-21	21-OCT-01	0001/10/21 00:00:00
01-OCT-21	21-OCT-01	0001/10/21 00:00:00
MODIFIED_DATETIME	TO_CHAR(MODIFIED_DATETIME,'YYYY/MM/DDHH24:MI:SS')
2021/10/01/13:00:00	2021/10/01 13:00:00
2021/10/01/12:30:00	2021/10/01 12:30:00