Re: SQL Date function question

[Bradley Stone <bvstone@xxxxxxxxx>]

Don,

The problem is the date is stored as 8,0P in the file as MMDDCCYY.

So while 10/10/2011 I can just char and substring 5-8 to get the year,
for 01/01/2011 I have to substring 4-7 to get the year.

That gets messy.  And no, we can't change the date format.  :)

And this is with dynamic SQL embedded in RPG.

Brad

On Wed, Jul 6, 2011 at 3:57 PM, Don Osmond <don.osmond@xxxxxxxxxxxxxx> wrote:
> If you use CCYYMMDD and the value return as 20110101 it will work.
>
> -----Original Message-----
> From: midrange-l-bounces@xxxxxxxxxxxx
> [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley Stone
> Sent: Wednesday, July 06, 2011 4:51 PM
> To: MIDRANGE-L@xxxxxxxxxxxx
> Subject: SQL Date function question
>
> I've got a program that uses dynamic SQL and I'm having a hard time
> finding documentation for this, or to find out if it's even possible.
>
> The file contains a date in the format MMDDCCYY.  I need to compare
> the year entered in a query with the year in the date.  At first I
> thought I could just substring off characters 5-8, but that won't work
> since for example:
>
> Jan 1, 2011
>
> is stored as
>
> 112011
>
> So obviously that won't work.  :)
>
> Does SQL on the iSeries have a way to convert this type of date to
> something and then yank the year out to compare it against a text
> literal?
>
> Thanks!
>
> Brad
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.