Oracle / Perl : Converting / formating the DBIx resultset date field

Based on the NLS_DATE_FORMAT setting in the oracle, query return the date field values. For example dd-mon-yyyy or dd-mm-yyyy hh:mm:ss or ... based on NLS_DATE_FORMAT.

While using direct query, we can use to_char or to_date to convert the date format from one to another date format.

Using DBIx Schema resultset and want to display the date in different format and you have question on how to do this in DBIx schema level then this article is for you.

Include the below in table schema file and access the field as usual which display the field in the specified format.

1. Set the NLS_Date_Format

use DateTime::Format::Oracle;

$ENV{'NLS_DATE_FORMAT'} = 'DD/MM/YYYY HH24:MI:SS';

This CPAN module DateTime::Format::Oracle have functions to convert the date data type into required format. Specify the NLS_Date_format the oracle use.

2. DBIx::Class::InflateColumn::DateTime module converts the date and datetime columns to date objects. To do this
include the below in table schema file

__PACKAGE__->load_components(qw/InflateColumn::DateTime/);

3. Specify the column data type as date or datetime field.

__PACKAGE__->add_columns(
emp_id,
emp_name,
join_date=>{date_type=>'date'},
);

4. __PACKAGE__->inflate_column('join_date',
{
inflate =>sub { my $dObj = DateTime::Format::Oracle->parse_datetime($dd);
return $dObj->strftime('%d-%b-%Y');},
deflate => sub { DateTime::Format::Oracle->format_datetime(shift);}
});

When you access the field in template or application, the inflate will be called which converts the date return from database resultset to required format.
The deflate converts date field for database for storage.

In the above example code, i just converted to dd-Mon-yyyy format using strftime. There are lot of functions available in DateTime, you can use those as required.

Hope this helps to understand and fix your issue. Post your experience and problem faced.