The date format in Oracle SQL Developer looks like DD-MON-YY.
For example 17-OCT-03.
The date format in APEX looks like MM/DD/YYYY.
For example 12/17/1980.
Why are they different?
This might cause the same SQL query not to work on both applications.
I know that I can avoid such problem by using TO_DATE and TO_CHAR functions but I want to understand the logic behind this problem.
Does every application use its own default date format?
Yes, every application has it's own date format.
And even every application can have more than one session, each with a different session format specified for DATEs and TIMESTAMPs.
SQL Developer has it's application level settings defined in the Preferences, Database, NLS page. This is how DATEs will appear unless you issue an ALTER SESSION SET... in your SQL Worksheet.
Or, if you always want a specific format regardless of this setting, build it into your query.
select to_char(sysdate, 'DAY') today from dual;
It's been my experience as well... YES, every Oracle application/tool can have its own default date format, but most just use the default display format set at the database level:
SQL> select name, value from v$parameter where name = 'nls_date_format';
NAME VALUE
--------------------- ---------------------
nls_date_format DD-Mon-YY
1 row selected.
But I would not characterize this as "a problem"... it is a valuable feature that provides lots of flexibility in displaying and entering dates in Oracle-based applications.
For sure though, if developers don't understand how Oracle dates work and write code such as this:
-- BAD Coding
DECLARE
ld_holiday_date DATE;
BEGIN
-- Set a date type variable to a string value and
-- hope that Oracle can figure out what I mean.
ld_holiday_date := '01-JAN-2022';
END;
they are writing environment specific code which will definitely not work in another database (or session) that has a different NLS_DATE_FORMAT from the one they wrote their code in. Furthermore, the code above is having to do an implicit data type conversion (from string to date) which is leaving it up to Oracle to try and figure out what format is in the string.
To write more deterministic code with regard to Oracle dates, developers should definitely use the TO_DATE function. Here's the bullet proof version of the code snippet above:
-- Good Coding
DECLARE
ld_holiday_date DATE;
BEGIN
-- I do not care what the NLS date format is in this
-- database... this code will work everywhere.
ld_holiday_date := TO_DATE ('01-JAN-2022', 'DD-MON-YYYY');
END;
