Touble With Dates

------=_Part_35650_17860437.1148580362310
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

The program below prints nothing when the date filter is in the where claus=
e
(TO_CHAR(SHIPDATE, 'MMDDYY') =3D 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 =3D '';
my $password =3D '';

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

### Prepare and execute an SQL statement
my $sth =3D $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 =3D s.ordernum and
TO_CHAR(SHIPDATE, 'MMDDYY') =3D TO_CHAR(SYSDATE-21, 'MMDDYY') and
actualservice =3D1 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 =3D $sth->{NUM_OF_FIELDS};
for ( my $i =3D 0 ; $i < $fields ; $i++ ) {

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

}
print "\n";

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


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

print join(",",@listref);

}

------=_Part_35650_17860437.1148580362310--

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

Re: Touble With Dates

Cliff Nadler wrote:
> 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.

It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses
should yield exactly the same results. For example:

SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY') ;

SHIPDATE
-------------------
2006-05-04 12:26:59

SQL> select * from x_ship
where trunc(shipdate) = trunc(sysdate-21);

SHIPDATE
-------------------
2006-05-04 12:26:59


I don't see any reason for your problem. You should turn on
tracing and run the query from sqlplus and from DBI and see
what the db is actually executing in each case.

I would add however that using the trunc is, IMO, preferred,
because the 1) the to_char character conversion serves no
purpose, and 2) it muddies the intention. You *want* to
compare *dates*, so you should compare dates.

Mark

Re: Touble With Dates

------=_Part_37481_9268618.1148586045550
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: quoted-printable
Content-Disposition: inline

That trick worked.

Thank you

On 5/25/06, Cliff Nadler wrote:
>
> Try the following instead:
>
> trunc(SHIPDATE) =3D 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') =3D TO_CHAR(SYSDATE-21, 'MMDDYY') ).
> >
> > The query work fine when I take this statement out. The query also work=
s
> > 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 =3D '';
> > my $password =3D '';
> >
> > my $dbh =3D DBI->connect( "dbi:Oracle:cbarch", $username, $password, {
> > RaiseError =3D> 1,ChopBlanks=3D>1});
> >
> > ### Prepare and execute an SQL statement
> > my $sth =3D $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 =3D s.ordernum and
> > TO_CHAR(SHIPDATE, 'MMDDYY') =3D TO_CHAR(SYSDATE-21, 'MMDDYY') and
> > actualservice =3D1 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 =3D $sth->{NUM_OF_FIELDS};
> > for ( my $i =3D 0 ; $i < $fields ; $i++ ) {
> >
> > my $name =3D $sth->{NAME}->[$i];
> > print $name .",";
> >
> > }
> > print "\n";
> >
> > $sth->execute() or die "Cannot Execute";
> >
> >
> > while (my @listref =3D $sth->fetchrow_array) {
> >
> > print join(",",@listref);
> >
> > }
> --
> ------------------------------------------------------------ -------------=
-
> Cliff Nadler Collective Technologies, LLC
> cnadler@colltech.com (512)-263-5500
> ------------------------------------------------------------ -------------=
-
> To win, you must treat a pressure situation as an opportunity to
> succeed,
> not an opportunity to fail Gardner Dickinson
>
>
>

------=_Part_37481_9268618.1148586045550--

RE: Touble With Dates

But who would want to have a query only match a down-to-the-second
timestamp exactly 21 days ago from the time of the query?

If the intent of the query is to find all entries made anytime during
the day 21 days ago, you will need the trunk function.

-Charlie


-----Original Message-----
From: mark [mailto:mark@immermail.com]=20
Sent: Thursday, May 25, 2006 3:40 PM
To: Joseph Lamb
Cc: dbi-users@perl.org
Subject: Re: Touble With Dates


Cliff Nadler wrote:
> Try the following instead:
>=20
> trunc(SHIPDATE) =3D trunc(SYSDATE)-21
>=20
> Oracle's date fields (and especially SYSDATE) are date + time. The=20
> TRUNC will trucate the value to the date only (actually midnight) so=20
> they can compare correctly.

It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses should
yield exactly the same results. For example:

SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') =3D to_char(sysdate-21, =
'MMDDYY')
;

SHIPDATE
-------------------
2006-05-04 12:26:59

SQL> select * from x_ship
where trunc(shipdate) =3D trunc(sysdate-21);

SHIPDATE
-------------------
2006-05-04 12:26:59


I don't see any reason for your problem. You should turn on tracing and
run the query from sqlplus and from DBI and see what the db is actually
executing in each case.

I would add however that using the trunc is, IMO, preferred, because the
1) the to_char character conversion serves no purpose, and 2) it muddies
the intention. You *want* to compare *dates*, so you should compare
dates.

Mark

Re: Touble With Dates

Hedstrom, Charlie wrote:
> If the intent of the query is to find all entries made anytime during
> the day 21 days ago, you will need the trunk function.

Nope.

SQL> select * from x_ship
where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')

SHIPDATE
-------------------
2006-05-04 00:01:29
2006-05-04 00:01:30
2006-05-04 00:01:31
2006-05-04 00:01:32
2006-05-04 00:01:33
2006-05-04 00:01:34
....


This query should clarify for you:

select shipdate,
to_char(shipdate, 'MMDDYY') as CHR_SHIP_DATE,
to_char(sysdate-21, 'MMDDYY') as CHR_SYSDATE_MINUS_21
from
x_ship where to_char(shipdate, 'MMDDYY') = to_char(sysdate-21, 'MMDDYY')


SHIPDATE CHR_SHIP_DATE CHR_SYSDATE_MINUS_21
------------------- ------------- --------------------
2006-05-04 00:01:29 050406 050406
2006-05-04 00:01:30 050406 050406
2006-05-04 00:01:31 050406 050406
2006-05-04 00:01:32 050406 050406
2006-05-04 00:01:33 050406 050406
2006-05-04 00:01:34 050406 050406
2006-05-04 00:01:35 050406 050406
2006-05-04 00:01:36 050406 050406


Mark

RE: Touble With Dates

An easier way to deal with this is to set the NLS_DATE_FORMAT at the
beginning of your program. This requires you to alter your session and
set the NLS_DATE_FORMAT. After that all your date will be displayed in
whatever format you choose.

An example is:

SQL> alter session set NLS_DATE_FORMAT =3D 'DD-MON-YYYY HH24:MI:SS';

Session altered.

SQL> select to_char(sysdate) from dual;

TO_CHAR(SYSDATE)
--------------------
25-MAY-2006 13:29:06

SQL> alter session set NLS_DATE_FORMAT =3D'MMDDYY';

Session altered.

SQL> select to_char(sysdate) from dual;

TO_CHA
------
052506

Every function adds execution cost to your script. If you want to
compare some dates to in the database to to-day, then I would grab one
of the PERL date modules, set a variable at the start of you program
with to-day's date, setup a placeholder in your prepare statement and
then drop the variable into the execute statement. This has the added
advantage of not having to run an extra query, to get sysdate, every
time your loop executes.

Hope this helps.

Ron

-----Original Message-----
From: Hedstrom, Charlie [mailto:Charlie.Hedstrom@netapp.com]=20
Sent: May 25, 2006 1:00 PM
To: mark; Joseph Lamb
Cc: dbi-users@perl.org
Subject: RE: Touble With Dates


But who would want to have a query only match a down-to-the-second
timestamp exactly 21 days ago from the time of the query?

If the intent of the query is to find all entries made anytime during
the day 21 days ago, you will need the trunk function.

-Charlie


-----Original Message-----
From: mark [mailto:mark@immermail.com]=20
Sent: Thursday, May 25, 2006 3:40 PM
To: Joseph Lamb
Cc: dbi-users@perl.org
Subject: Re: Touble With Dates


Cliff Nadler wrote:
> Try the following instead:
>=20
> trunc(SHIPDATE) =3D trunc(SYSDATE)-21
>=20
> Oracle's date fields (and especially SYSDATE) are date + time. The
> TRUNC will trucate the value to the date only (actually midnight) so=20
> they can compare correctly.

It would be curious if trunc works, but there is nothing
wrong with the way Joseph was doing it -- both where clauses should
yield exactly the same results. For example:

SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') =3D to_char(sysdate-21, =
'MMDDYY')
;

SHIPDATE
-------------------
2006-05-04 12:26:59

SQL> select * from x_ship
where trunc(shipdate) =3D trunc(sysdate-21);

SHIPDATE
-------------------
2006-05-04 12:26:59


I don't see any reason for your problem. You should turn on tracing and
run the query from sqlplus and from DBI and see what the db is actually
executing in each case.

I would add however that using the trunc is, IMO, preferred, because the
1) the to_char character conversion serves no purpose, and 2) it muddies
the intention. You *want* to compare *dates*, so you should compare
dates.

Mark

Re: Touble With Dates

"Joseph Lamb" schreef:

> s.shipdate
>
> TO_CHAR(SHIPDATE, 'MMDDYY') = TO_CHAR(SYSDATE-21, 'MMDDYY') and


Maybe you need to replace "SHIPDATE" by "s.shipdate"?

--
Affijn, Ruud

"Gewoon is een tijger."

Re: Touble With Dates

Warden, Ronald MCF:EX wrote:
> An easier way to deal with this is to set the NLS_DATE_FORMAT at the
> beginning of your program. This requires you to alter your session and
> set the NLS_DATE_FORMAT. After that all your date will be displayed in
> whatever format you choose.

Note that the original poster was not *displaying* dates. He was doing
date comparisons for results selection (ie, the where clause)

Relying on NLS_DATE_FORMAT for this purpose can be dangerous practice,
because it means your SQL has different meaning depending on
the context in which the SQL is executed, eg:

select .. where SOME_DATE_COL = ?

You can't tell what that does without knowing the session
settings. Compare to:

select .. where trunc(SOME_DATE_COL) = trunc(?)

which is explicit, and consistent regardless session settings.

If you have absolute, guaranteed control over session context
for the lifetime of your code, great. Otherwise, the latter
is generally better practice.


> Every function adds execution cost to your script. If you want to
> compare some dates to in the database to to-day, then I would grab one
> of the PERL date modules, set a variable at the start of you program
> with to-day's date, setup a placeholder in your prepare statement and
> then drop the variable into the execute statement. This has the added
> advantage of not having to run an extra query, to get sysdate, every
> time your loop executes.

Perhaps, but what *how much* something costs matters too
(and trunc and sysdate are pretty cheap)

"Premature optimization is the root of all evil" -- Donald Knuth

Mark

RE: Touble With Dates

I would only add that I believe the to_char method is more expensive
because you are taking temporal data, converting it to a string
representation and then doing a string comparison. Really not a big
deal but this can be quite expensive with large data sets. Then again
with a large data set (assuming shipdate is indexed) I would write it
out like

where shipdate >=3D trunc(sysdate-21)
and shipdate < trunc(sysdate-20)

NOTE: it is generally a bad idea to use a function on left side of =3D.
This will cause a full table scan.

-----Original Message-----
From: mark [mailto:mark@immermail.com]=20
Sent: Thursday, May 25, 2006 1:40 PM
To: Joseph Lamb
Cc: dbi-users@perl.org
Subject: Re: Touble With Dates

Cliff Nadler wrote:
> Try the following instead:
>=20
> trunc(SHIPDATE) =3D trunc(SYSDATE)-21
>=20
> Oracle's date fields (and especially SYSDATE) are date + time. The=20
> TRUNC will trucate the value to the date only (actually midnight) so=20
> they can compare correctly.

It would be curious if trunc works, but there is nothing wrong with the
way Joseph was doing it -- both where clauses should yield exactly the
same results. For example:

SQL>select * from x_ship
where to_char(shipdate, 'MMDDYY') =3D to_char(sysdate-21, =
'MMDDYY')
;

SHIPDATE
-------------------
2006-05-04 12:26:59

SQL> select * from x_ship
where trunc(shipdate) =3D trunc(sysdate-21);

SHIPDATE
-------------------
2006-05-04 12:26:59


I don't see any reason for your problem. You should turn on tracing and
run the query from sqlplus and from DBI and see what the db is actually
executing in each case.

I would add however that using the trunc is, IMO, preferred, because the
1) the to_char character conversion serves no purpose, and 2) it muddies
the intention. You *want* to compare *dates*, so you should compare
dates.

Mark