RE: embedded sql date select

Thanks Joe

don't know exactly where my brain was.

With your help my solution looks like this

S = %trim(S) + ' AND '
+ '('
+ 'DEC('
+ ' SUBSTR(DIGITS(AHBSDT),5,2)'
+ ' CONCAT'
+ ' SUBSTR(DIGITS(AHBSDT),1,4)'
+ ' ,6,0)'
+ ' BETWEEN '
+ SQFYY + SQFMM + SQFDD
+ ' AND '
+ SQTYY + SQTMM + SQTDD
+ ')';
endIF;
Jim Horn

------------------------------

message: 3
date: Thu, 11 Oct 2007 11:37:57 -0500
from: "Joe Pluta" <joepluta@xxxxxxxxxxxxxxxxx>
subject: RE: embedded sql date select

Well, you could convert the field from MDY to YMD using DIGITS and
SUBSTR
and DECIMAL, something like:

DECIMAL(
SUBSTR(DIGITS(MYDATE),5,2) CONCAT
SUBSTR(DIGITS(MYDATE),1,4), 6, 0)

Once you have it in a usable format, you can just use the BETWEEN
operator.
Of course, if you're dealing with pre-2000 dates you'll need to take
into
account the century.

Joe

> From: JDHorn@xxxxxxxxxxxxxx
>
> I am attempting to select records in a file on a date stored as
mdy in
> a
> 6s 0 field.
>
> could use the between option or as greater than one date and less
than
> another.
>
> so far my solutions are far from elegant.
>
> does anyone have an example of an rpg program to generate an sql
select
> statement to do this?
>
> the date fields in the file may have zeros or other invalid dates
in
> them