Re: SQL Problem Coverting from Numeric YYYYMMDD to a Date Format

[Mike <koldark@xxxxxxxxx>]

Does this work in SQL? I this looks like RPG. I am trying to do this in pure
SQL if possible for simplicity.

On 7/19/06, Michael_Schutte@xxxxxxxxxxxx <Michael_Schutte@xxxxxxxxxxxx>
wrote:
> If it's in YYYYMMDD format, no need to convert to date.
>
> Just
>
> V5R3
> RangeBegin = %Dec(%Date(YYYYMMDD:*ISO) - %Years(1):*ISO);
> RangeEnd = YYYYMMDD;
>
> Select * from file
> where YYYYMMDD between (:RangeBegin and :RangeEnd)
>
> or something similar to that.
>
>
>
> Michael Schutte
>
> Advice given may not be completely accurate, I depend on you to interrupt
> what I type.
>
>
>
>
>              Mike
>              <koldark@xxxxxxxx
>              m>                                                         To
>              Sent by:                  "RPG programming on the AS400 /
>              rpg400-l-bounces@         iSeries" <rpg400-l@xxxxxxxxxxxx>
>              midrange.com                                               cc
>
>                                                                    Subject
>              07/19/2006 12:58          SQL Problem Coverting from Numeric
>              PM                        YYYYMMDD to a Date Format
>
>
>              Please respond to
>               RPG programming
>               on the AS400 /
>                   iSeries
>              <rpg400-l@midrang
>                   e.com>
>
>
>
>
>
>
> I am trying to create a view that automatically displays only 1 year's
> worth
> of data. The problem is that the dates are in a numeric YYYYMMDD format.
> What is the easiest why to convert to a date format?
>
> This is what I am trying now and doesn't work.:
> CAST(CONCAT(CONCAT(SUBSTRING(CAST(ncmplntdt as char),1,4), '-'),
> CONCAT(CONCAT(SUBSTRING(CAST(ncmplntdt as char),5,2), '-'),
> SUBSTRING(CAST(ncmplntdt as char),7,2))) AS DATE) as ncmplntdt
>
> The always helpful IBM error is "Argument 1 of function *N not valid."
>
> I am sure there has to be an easier way. We are on V5R3.
>
>
> --
> Mike Wills
> koldark@xxxxxxxxx
> http://theriverbendpodcast.com
> --
> This is the RPG programming on the AS400 / iSeries (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.
>
>
>
> --
> This is the RPG programming on the AS400 / iSeries (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.