Re: SQL Date function question

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Brad,

I find it very hard to believe that Jan 1, 2011 is stored as
112011

What would Nov 1 2011 be stored as?
1112011

How about Jan 11 2011
1112011

See the problem? :)

My guess is the value stored is 01012011, but you see 1012011 when
using CAST or CHAR...

What you need is DIGITS

SUBSTR(DIGITS(datefld),5,4) as theYear.

HTH,
Charles


On Wed, Jul 6, 2011 at 4:51 PM, Bradley Stone <bvstone@xxxxxxxxx> wrote:
> 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.