Date Time Format, Perl Oracle SQL

Question

I am running the below script and I am having an issue displaying the time fieldsEFFECTIVE_TIME and SUBMIT_TIME. These are actually displaying blank. I need to display it in MM/DD/YYYY HH:MI:SS AM. Any ideas?

#!/usr/bin/perl
use DBI;
unlink("D:\\Perl32\\scripts\\NEW.html");

my $host = '${Node.Caption}';
my $user = '${USER}';
my $pwd = '${PASSWORD}';

my $driver = "Oracle";
$dsnslam = "DBI:$driver:host=$host;service_name=PROD;port=1727";
$dbhslam = DBI->connect($dsnslam, "$user", "$pwd") || die "connect failed: ";
$sthslam = $dbhslam->prepare("select mti.FACILITY, TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_SUBMIT_TIME, TO_CHAR(MAX(td.SUBMIT_TIME), 'MM/DD/YYYY HH:MI:SS AM') AS MAX_EFFECTIVE_TIME
from fl.erip_data td, fl.managed_eripid mti
where td.insert_TIME>sysdate-1
and td.ERIP_ID=mti.ERIP_ID and td.PLAZA_ID=mti.PLAZA
group by mti.FACILITY");

$sthslam->execute;
$msg = "Up";
$Count = 0;
$Output = "";
$Temp="";
$tbl = "<TABLE border=1  bordercolor=orange cellspacing=0 cellpadding=1>";
$tblhd = "<TR><TH>FACILITY</TH><TH align=center>MAX(TD.SUBMIT_TIME)</TH><TH align=center>MAX(TD.EFFECTIVE_TIME)</TH>";

while(  my $ref = $sthslam->fetchrow_hashref() ) {
        $Count++; 
        $Temp = "$Temp<TR><TD align=left rowspan=1 valign=top height=10>$ref->{'FACILITY'}</TD><TD align=left rowspan=1 valign=top height=10>$ref->{'MAX(TD.SUBMIT_TIME)'}</TD><TD align=center rowspan=1 valign=top height=10>$ref->{'MAX(TD.EFFECTIVE_TIME)'}</TD>";
        $Output = "$Output$Temp";
        $Temp="";
}
$dbhslam->disconnect;
$Output="$tbl$tblhd$Output</TABLE>";
my $filename1 = 'D:\\Perl32\\Scripts\\NEW.html';
open(my $fh1, '>', $filename1) or die "Could not open file '$filename1' $!";
print $fh1 "$Output";
close $fh1;

if ( $Count > 0 )
{
$msg = $Output;
}
elsif ( ($Count == 0) || (!defined $Count) )
{
    $Count = 0;
    $msg="NO ROWS RETURNED";
}

print "\nMessage: $msg";
print "\nStatistic: $Count";

Answer 1

You give the columns aliases MAX_SUBMIT_TIME, MAX_EFFECTIVE_TIME but you do not use the aliases when you are accessing the data:

$Temp = "$Temp<TR><TD align=left rowspan=1 valign=top height=10>$ref->{'FACILITY'}</TD><TD align=left rowspan=1 valign=top height=10>$ref->{'MAX_SUBMIT_TIME'}</TD><TD align=center rowspan=1 valign=top height=10>$ref->{'MAX_EFFECTIVE_TIME'}</TD>";