Re: SQL and Dates, help needed

An ad hoc query from my history - HRPRDT is 8p0 in the database.
select * from mhtr where
date(substr(char(hrprdt),1,4) concat '-' concat
substr(char(hrprdt),5,2) concat '-' concat substr(char(hrprdt),7,2))
= current date - 1 day


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Al Nelson
Sent: Thursday, September 08, 2011 12:15 PM
To: RPG400-L@xxxxxxxxxxxx
Subject: SQL and Dates, help needed

I'm trying to select all the records in a file that have yesterday's
date.
The field in the file is defined as numeric 8 positions and 0 decimals.
It is in a YYYYMMDD format.

I would think a Select with Current Date - 1 would work but is does not.

any suggestions.

thanks

al

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing
list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.




Kevin Bucknum
Senior Programmer Analyst
MEDDATA/MEDTRON
Tel: 985-893-2550

--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.