RE: SQL Date function question

["Monnier, Gary" <Gary.Monnier@xxxxxxxxx>]

Brad,

Just out of curiosity what does DSPPFM (F10 + F11 for over/under hex) show? Are leading zeros stripped off?

Gary Monnier

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Bradley Stone
Sent: Wednesday, July 06, 2011 2:41 PM
To: Midrange Systems Technical Discussion
Subject: Re: SQL Date function question

Paul,

Yes, that is what I was using.  Then we found out nothing before
10/1/xxxx was being picked up.  That's when I looked into the file.

Funny thing is, in the DDS it has an edit code of Y, so when I SQL or
Query interactivley the date shows fine with separators.

But when I UPDDTA on the file, I see the issue.  It's a number, so
it's right justified without any leading zero if the month is less
than 10.  How many decimal fields have you seen with leading zeros...
is the number 1 stored in a 4,0P field going to be the value 0001?
Hell no!  lol

It IS lunacy.  I agree.  All I know is that's what I have to work with... lol

Brad

On Wed, Jul 6, 2011 at 4:05 PM, Morgan, Paul <Paul.Morgan@xxxxxxxxxxx> wrote:
> Brad,
>
> Stored as 112011 is not MMDDCCYY.  You sure it's not stored as 01012011?
>
> SUBSTR(DIGITS(MDYYdate),5) = '2011' if it's stored as 01012011.
>
> Paul Morgan
>
> Principal Programmer Analyst
> IT Supply Chain/Replenishment
>
>
> -----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.