Try the following instead:
trunc(SHIPDATE) = trunc(SYSDATE)-21
Oracle's date fields (and especially SYSDATE) are date + time. The
TRUNC will trucate the value to the date only (actually midnight) so
they can compare correctly.
On Thu, 2006-05-25 at 14:06 -0400, Joseph Lamb wrote:
> 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);
>
> }
--
--------------------------------------------------------------------------
Cliff Nadler Collective Technologies, LLC
[EMAIL PROTECTED] (512)-263-5500
--------------------------------------------------------------------------
To win, you must treat a pressure situation as an opportunity to
succeed,
not an opportunity to fail Gardner Dickinson
