Touble With Dates

[Joseph Lamb]

The program below prints nothing when the date filter is in the where clause
(TO_CHAR(SHIPDATE, 'MMDDYY') = TO_CHAR(SYSDATE-21, 'MMDDYY') ).

The query work fine when I take this statement out. The query also works
fine when I run it form the CLI.

What do I need to do to make this program work? Your help is appreciated.


++++++++++++++++++++++++++++++++++
use DBI;
use strict;

### Connect to the database
my $database;
my $username = '';
my $password = '';

my $dbh = DBI->connect( "dbi:Oracle:cbarch", $username, $password, {
   RaiseError => 1,ChopBlanks=>1});

### Prepare and execute an SQL statement
my $sth = $dbh->prepare("SELECT
 s.REFNUMBER,
 s.ORDERNUM,
 o.MEMBERFIRSTNAME,
 o.MEMBERLASTNAME,
 o.mEMBERADDRESSLINE1,
 o.mEMBERADDRESSLINE2,
 o.MEMBERADDRESSCITY,
 o.MEMBERADDRESSSTATE,
 o.MEMBERADDRESSZIP5,
 s.shipdate
FROM
 orderstatic o,
 shippingpackage s
WHERE
 o.ordernum = s.ordernum and
   TO_CHAR(SHIPDATE, 'MMDDYY') = TO_CHAR(SYSDATE-21, 'MMDDYY') and
 actualservice =1 and
 SUBSTR(s.REFNUMBER,9,2)   in
('35','24','19','39','42','29','A2','58','A3','A4','A5','A6','A7','A8')");

print "Statement:     $sth->{Statement}\n";

my $fields = $sth->{NUM_OF_FIELDS};
for ( my $i = 0 ; $i < $fields ; $i++ ) {

   my $name = $sth->{NAME}->[$i];
   print $name .",";

}
print "\n";

$sth->execute() or die "Cannot Execute";


while (my @listref = $sth->fetchrow_array) {

   print join(",",@listref);

}