[Catalyst] SQLite Dates

[Dami Laurent (PJ)]

> Date: Fri, 17 Mar 2006 22:17:31 +0000
> From: Kevin White <kevin at theconfused.co.uk>
> Subject: [Catalyst] SQLite Dates
> To: catalyst at lists.rawmode.org
> Message-ID: <CFE925E5-206B-4C4A-839D-93AACCBBB90C at theconfused.co.uk>
> Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
> 
> Hi folks,
> 
> I am using SQLite for a quick app and within the app are some dates  
> that I am presenting in dd-MMM-YYYY. Trouble is SQLite requires them  
> in YYYY-MM-DD HH::MM::SS for it to do anything useful with. 
> How would  
> I go about converting the incoming dates to the correct DB 
> format and  
> vice versa? Anyone got any examples or could point me to one?
> 
> Thanks for any help
> 
> Kevin
> 


Hi Kevin,

I had a similar problem with Ms Access. This was one of the many reasons
why I wrote DBIx::DataModel.
So if you are willing to replace CDBI by DBIx::DataModel, you can define
converters for your columns.
Here is an excerpt from the synopsis :

# Declare a "Date" column type with some handlers (conversion between 
# database (yyyy-mm-dd) and user (dd.mm.yyyy) )

  MySchema->ColumnType(Date => 
     fromDB   => sub {$_[0] =~ s/(\d\d\d\d)-(\d\d)-(\d\d)/$3.$2.$1/},
     toDB     => sub {$_[0] =~ s/(\d\d)\.(\d\d)\.(\d\d\d\d)/$3-$2-$1/},
     validate => sub {$_[0] =~ m/(\d\d)\.(\d\d)\.(\d\d\d\d)/});
  
# apply this ``column types'' to some of our tables/columns

  Employee->ColumnType(Date    => qw/d_birth/);
  Activity->ColumnType(Date    => qw/d_begin d_end/);

Best regards, L. Dami