Handling MS-Access date time

k_rajesh has asked for the wisdom of the Perl Monks concerning the following question:

Problem: Date fields in Access are returned as dd/mm/yyyy hh:mi:ss and printed as such on my HTML output. Reasons for problem: I have a generic function that parses db output and converts into an arrayref that is passed as arguments to the TMPL_LOOP attribute of HTML::Template. This function does not know about the data types it receives. The way the SELECT query is framed takes care that I dont have to do individual field assignments to map attributes in the template to the SELECTed fields. Code attached:

sub get_tmpl_loop_arrayref_bind
#Given an SQL query this will return an arrayref compatible with TMPL_
+LOOP. Works where the query does not contain a , before the 'from' an
+d where there is no subquery.
{
my ($dbh, $sql, @binds, $sessID) = @_;
$dbh->{RaiseError}=0;
$dbh->{PrintError}=0;
$sql=~/select\s+(.*?)from\s+.*/ig;
my $onlyfields = $1;
my @fields = split /,/, $onlyfields; 
foreach my $field (@fields) 
{
$field=$1 if ($field=~/.*?\s+as\s+\[(.*)\]/ig); #Fields taken with the
+ as identifier are parsed here. Eg. "select a/b as ratio"
$field=~s/\s+//ig;
}

my $sth = $dbh->prepare($sql) || die "Could not prepare query $sql in 
+get_tmpl_loop_arrayref";
$sth->execute || die "Could not execute query $sql in get_tmpl_loop_ar
+rayref";
my @loop_data = ();
while (my $hash_op = $sth->fetchrow_hashref('NAME_lc')) 
    {
            my %row_data;
            foreach my $field (@fields) 
            {
                $row_data{$field}=$hash_op->{$field}? $hash_op->{$fiel
+d}:'-' ;
            }
            $row_data{id}=$sessID if $sessID;
            push (@loop_data, \%row_data);
    }
if ($dbh->errstr()) {
    return $dbh->errstr();
}
else
    {return \@loop_data;}
}
[download]

The SELECT statement would be as follows:

"SELECT username, name, role, email, phone, mobile, creation_date from
+ user where creator='".$session->param('username')."'";
[download]

and the HTML:Template would be like so:

<TMPL_LOOP NAME=all_users>
<!-- Data display -->
<TR>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=username></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=name></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=role></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=email></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=phone></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=mobile></TD>
<TD WIDTH="25%" class="tblvlu"><TMPL_VAR NAME=creation_date></TD>
[download]

Works like a charm except the display of the creation date which is usually something like
10/06/2005 00:00:00
Regards,
Rajesh
PS: Was that too much detail??

Comment on Handling MS-Access date time Select or Download Code

Replies are listed 'Best First'.
Re: Handling MS-Access date time by Zed_Lopez (Chaplain) on Oct 08, 2005 at 07:44 UTC
Too little detail in one respect: I don't know what different behavior you'd like to see. But here's a way to transform a field while keeping the inside of your loop nice and field-agnostic -- I'll guess that you want to strip the time and display just the date. use Data::Dumper; my %field_filter = (creation_date => sub { my $datetime = shift; retur +n (split ' ', $datetime)[0] } ); my @fields = (qw(creation_date a b)); my @hash_ops = ({a => '', b => 2, creation_date => '12/31/2005 12:32:1 +1'}, {a => 5, b => 0, creation_date => '23/01/2222 13:14:15'}); my @loop_data; for my $hash_op (@hash_ops) { my %row_data; foreach my $field (@fields) { if ($hash_op->{$field}) { $row_data{$field} = exists $field_filter{$field} ? $field_filter +{$field}->($hash_op->{$field}) : $hash_op->{$field}; } else { $row_data{$field} = '-'; } } push (@loop_data, \%row_data); } print Dumper(\@loop_data) [download] This outputs: `$VAR1 = [ { 'a' => '-', 'creation_date' => '12/31/2005', 'b' => 2 }, { 'a' => 5, 'creation_date' => '23/01/2222', 'b' => '-' } ];` [download]	[reply] [d/l] [select]
Re: Handling MS-Access date time by Errto (Vicar) on Oct 08, 2005 at 21:34 UTC
One simple way is you can just change your SQL so that instead of selecting `creation_date` you select `TO_CHAR('mm/dd/yyyy', creation_date) as creation_date` [download] Another way would be to default the display of all DATE columns in a particular format. You can use the TYPE statement handle attribute as documented in DBI and if the value matches SQL_DATE or SQL_DATETIME you can then have a special condition to format the data from that column accordingly (which could be a simple regexp-replace since you already know the format the DBI is going to use by default). Note: I've never done this in Perl but I've done the equivalent in Java (using ResultSetMetaData) and it works pretty well. Regarding your question, I agree with Zed_Lopez that your level of detail was just right, except that you forgot to say what you wanted the output to be instead of what it is now.	[reply] [d/l] [select]