Re: Touble With Dates

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
cnadler@xxxxxxxxxxxx (512)-263-5500
--------------------------------------------------------------------------
To win, you must treat a pressure situation as an opportunity to
succeed,
not an opportunity to fail Gardner Dickinson


.