The following example tries to demonstrate how the value of
nls_date_format influences a
where clause condition.
Save current value of nls_date_format so we can restore it later.
select sys_context('userenv', 'nls_date_format') from dual;
Change value of nls_date_format:
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
begin
dbms_session.set_nls('nls_date_format', '"yyyy-mm-dd hh24:mi:ss"');
end;
/
Verify value was changed:
select value from v$parameter where name = 'nls_date_format';
Select a few
created values from
dba_objects. They're returned in the specified format:
select
owner,
object_name,
object_type,
created
from
dba_objects;
Pick one of the selected values and select all objects that were created in the given second:
select * from dba_objects where created = '2023-03-26 02:26:30';
Change date format:
alter session set nls_date_format = 'yyyy-mm-dd';
select * from dba_objects where created = '2023-03-26 02:26:30';
One might be tempted to change the where condition. But this query doesn't (very likely) find anything, as the string 2023-03-26 is exactly midnight:
select * from dba_objects where created = '2023-03-26';
Using
to_date to make sure we're independent from the date format. This query will return some records:
select * from dba_objects where created = to_date('2023-03-26 02:26:30', 'yyyy-mm-dd hh24:mi:ss');
Possibly a more lightweight solution to select records using the timestamp literal:
select * from dba_objects where created = timestamp '2023-03-26 02:26:30';