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

["Fleming, Greg \(ED\)" <GFLEMING@xxxxxxxxxxxxxxxxxxxx>]

Or if you don't want to do any conversions, just subtract 10000 from
your end date and you'll have your beginning date.



|-----Original Message-----
|From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]
|On Behalf Of Michael_Schutte@xxxxxxxxxxxx
|Sent: Wednesday, July 19, 2006 1:12 PM
|To: RPG programming on the AS400 / iSeries
|Subject: Re: SQL Problem Coverting from Numeric YYYYMMDD to a Date
Format
|
|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.