Oracle date format and Perl

Posted: September 24, 2009/Under: Perl Stuff/By: Michael Stepanov

Oracle has its own date format – DD-MM-YY (24-sep-09). So, to insert a value into Oracle date field you should somehow convert date from your format. Sure it can be done using some date/time module or your own function. But there are two simpler ways – set desire date/time format for session or use Oracle date conversion function.

To set specific date/time format for the session just run following query right after connection:
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'
After that you may insert date such 2009-09-24 without any problems.

Another way is convert date to the Oracle format inside the query:
INSERT into some_table (d_start) VALUES (TO_DATE('2009-09-24 11:12:00', 'YYYY-MM-DD HH24:MI:SS'))

Both approaches work fine. But the first one is more appropriate if you use placeholders in your query.

Oracle date format and Perl