Oracle: NLS_DATE

Influence of NLS_DATE_FORMAT to selecting records

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

Alternatively, use dbms_session.set_nls (but this doesn't seem to work in SQL Developer:

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

Try the same query. It throws ORA-01830: date format picture ends before converting entire input string.

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

Oracle: NLS_DATE_FORMAT

Influence of NLS_DATE_FORMAT to selecting records

See also