Touble With Dates

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);

}

Relevant Pages

  • Re: Select Query
    ... For some reason, Access does not recognize every filter and ... gives me exactly what I'm trying to filter out in the WHERE clause. ... a way to make this query work using only one query or do I have to build ...
    (microsoft.public.access.queries)
  • Re: Touble With Dates
    ... > The query work fine when I take this statement out. ... > What do I need to do to make this program work? ... > my $database; ...
    (perl.dbi.users)
  • Re: Touble With Dates
    ... TRUNC will trucate the value to the date only so ... The query work fine when I take this statement out. ... my $database; ...
    (perl.dbi.users)
  • Re: Oracle to dbf, again
    ... Does the query work if you insert it directly in the SQLEXEC function? ... there an error message you can pick up with AERROR? ... different database?), without any references to primary and foreign keys ... > TEXT TO lcSQL NOSHOW ...
    (microsoft.public.fox.vfp.queries-sql)
  • Query a database on a server not working
    ... database to my local drive, ... query work with the database on the server? ... Prev by Date: ...
    (microsoft.public.excel.misc)