Perl DBD::ODBC Issues with Oracle Date Formats

Question

I am using Perl's DBD::ODBC to connect to an Oracle database. However, an issue arises when I try to execute a select query using a date in the where clause. It seems this issue occurs because of the database's date format being DD-MON-RR (see DBD::ODBC::FAQ). Since I cannot change the database's settings, can anyone suggest a workaround?

Answer 1

The database's default date format only matters if you depend on it, which you should not in general. You can:

1) Specify the format of the date in your query:

select *
from news
where news_date = to_date ('01-DEC-2009','DD-MON-RRRR');

2) Use the ANSI standard for date literals:

select *
from news
where news_date = DATE '2009-12-01';

Answer 2

Hi indiguy,

Don't rely on implicit datatype conversion. You can always specify the date format in the where clause:

WHERE your_column = to_date(:your_parameter, 'yyyy/mm/dd')

Answer 3

One option is to use the TO_DATE() function (or the ANSI 'DATE' keyword) to convert the format in every query:

WHERE date_field > TO_DATE('2009-11-01', 'YYYY-MM-DD');
-- or
WHERE date_field > DATE '2009-11-01'

If you have to do this a lot, a better option would be to set the format for the session:

$dbh->do("ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
$dbh->do("ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSxFF'");
$dbh->do("ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SSxFF'");

Then:

my $sth = $dbh->prepare(<<EOT);
SELECT date_field
FROM some_table
WHERE date_field > '2009-11-01'
EOT