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

Hi,

if you want to convert a numeric date into a real date, you can use the
following statement

Date(Substr(Digits(MyDate), 1, 4) concat '-' concat
     Substr(Digits(MyDate), 5, 2) concat '-' concat
     Substr(Digits(MyDate), 7, 2))

I think it's a little more readeable than yours.

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Mike
Gesendet: Mittwoch, 19. Juli 2006 18:58
An: RPG programming on the AS400 / iSeries
Betreff: SQL Problem Coverting from Numeric YYYYMMDD to a Date Format


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.