Working with dates in Oracle

Recently I had an issue where a stored procedure was tested by a
developer and worked perfectly. When the stored procedure got migrated
to test, a user tried to execute it but it did not work. The problem
took a while to track down but in the end it was because the stored
procedure did not explicitly define the date format when converting a
character string into a date. The developer and the tester had different
settings for nls_date_format and this was causing the problem. Below is
an example of how this can happen.

Create the table

control@DWDEV> create table visit
2 (visit_date date
3 ,visit_place varchar2(20));

Table created.

Insert in some values

control@DWDEV> insert into visit values (to_date('20060403','YYYYMMDD'), 'Zoo');

1 row created.

control@DWDEV> insert into visit values (to_date('20060403','YYYYMMDD'), 'Park');

1 row created.

control@DWDEV> insert into visit values (to_date('20060402','YYYYMMDD'), 'Bank');

1 row created.

Select all records

control@DWDEV> select * from visit;

VISIT_DATE VISIT_PLACE
----------- --------------------
03-APR-2006 Zoo
03-APR-2006 Park
02-APR-2006 Bank

Select using to_date function with format mask

control@DWDEV> select * from visit where visit_date = to_date('03-Apr-2006','DD-Mon-YYYY');

VISIT_DATE VISIT_PLACE
----------- --------------------
03-APR-2006 Zoo
03-APR-2006 Park

Select using to_date without a format mask, lucky it works but....

control@DWDEV> select * from visit where visit_date = to_date('03-Apr-2006');

VISIT_DATE VISIT_PLACE
----------- --------------------
03-APR-2006 Zoo
03-APR-2006 Park

Alter the nls_date_format

control@DWDEV>
control@DWDEV> alter session set nls_date_format = 'YYYYMMDDHH24MISS';

Session altered.

Select using to_date without a format mask, this time we are out of luck

control@DWDEV> select * from visit where visit_date = to_date('03-Apr-2006');
select * from visit where visit_date = to_date('03-Apr-2006')
*
ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was
expected

However our original query still works
Select using to_date function with a format mask

control@DWDEV> select * from visit where visit_date = to_date('03-Apr-2006','DD-Mon-YYYY');

VISIT_DATE VISIT_PLACE
-------------- --------------------
20060403000000 Zoo
20060403000000 Park

So the moral of the story is to always explicity specify the format when
converting a character string to a date. Any functions that rely on a
client setting should be avoided because just by having a different
person run the code, it may break.