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

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.