What is Oracle's Default Date Format?

Question

I have an Oracle DB, and I don't control the date format. I want to know what the date format is to ensure that searches like

select * from search where search_date>='03/16/2016 00:00:00'

work as expected.

Answer 1

Don't do that - you are relying on implicit data type conversion which is going to fail at some point.

You have two options:

1) Use a proper ANSI SQL date literal:

select * 
from search 
where search_date >= timestamp '2016-03-16 00:00:00';

2) use to_date() (or to_timestamp()) and use a custom format.

select * 
from search 
where search_date >= to_date('03/16/2016 00:00:00', 'mm/dd/yyyy hh24:mi:ss');

With to_date() you should avoid any format that is language dependent. Use numbers for the month, not abbreviations (e.g. 'Mar' or 'Apr') because they again rely on the client language.

More details can be found in the manual:
https://docs.oracle.com/cd/E11882_01/server.112/e41084/sql_elements003.htm#SQLRF51062

---

Never rely on implicit data type conversion.

Answer 2

You can get all the NLS session parameters with the query:

SELECT * FROM NLS_SESSION_PARAMETERS;

or, if you have the permissions GRANT SELECT ON V_$PARAMETER TO YOUR_USERNAME;, you can use the command:

SHOW PARAMETER NLS;

If you just want the date format then you can do either:

SELECT * FROM NLS_SESSION_PARAMETERS WHERE PARAMETER = 'NLS_DATE_FORMAT';

or

SHOW PARAMETER NLS_DATE_FORMAT;

However, you could also use ANSI date (or timestamp) literals which are format agnostic. An ANSI date literal has the format DATE 'YYYY-MM-DD' and a timestamp literal has the format TIMESTAMP 'YYYY-MM-DD HH24:MI:SS.FF9'. So your query would be:

select * from search where search_date>= DATE '2016-03-16'

or

select * from search where search_date>= TIMESTAMP '2016-03-16 00:00:00'