Re: SQL and Dates, help needed

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Bad idea...

Since you're converting the data in the table column, the system will
not be able to use an index...

As Chuck and I already mentioned better to convert the (current date -
1 day) into a CCYYMMDD numeric.

Charles

On Thu, Sep 8, 2011 at 1:55 PM, Kevin Bucknum <Kevin@xxxxxxxxxxxxxxxxxxx> wrote:
> 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.