[Dbix-class] Inflating and deflating Oracle dates

Dan Horne dan.horne at redbone.co.nz
Mon Jun 5 03:12:12 CEST 2006

I'm trying to figure out how to inflate/deflate Oracle dates. Oracle doesn't
distinguish between date and datetime datatypes. If you don't provide a time
component, the time defaults to '00:00'

In addition, Oracle assumes a default date format of 'DD-MON-YY', or
'%d-%b-%y' in POSIX-speak. If you use a different format, then you need to
tell Oracle:

insert into test(date_column) values ('01-JUN-2006 12:17', 'DD-MON-YYYY

In other words, while there may be a canonical datetime format for a DB like
MySQL, Oracle lets you specify your preferred format when selecting and

There are ways around this. You can declare alternate default date formats
at both the database (global) or session levels. Since most DBAs would baulk
at the first approach, we could issue something like this:

alter session set NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS';

This would probably need to be issued on connect, but how would you go about
doing this? Can you provide callbacks to say a method in the Oracle storage


Dan Horne 

More information about the Dbix-class mailing list