Re: Formatting a DATE differently using SQL

Yes that does make it wordy. Will this work?

First create this:
create function QUSRSYS/CLAYDATE
(inputdate date)
returns char(8)
language sql
contains sql
no external action
deterministic
returns null on null input
return (substr(char(inputdate, USA),1,6) concat
substr(char(inputdate, USA),9,2))

Then this:
SELECT CHAR(StartDate, LOCAL), CHAR(EndDate, LOCAL) FROM (SELECT
MIN(dayid) as StartDate, MAX(dayid) AS EndDate FROM mySchema.ludate
WHERE yearid = '2014' AND datemn = '01' ) A

becomes:
SELECT CLAYDATE(StartDate), CLAYDATE(EndDate) FROM (SELECT
MIN(dayid) as StartDate, MAX(dayid) AS EndDate FROM mySchema.ludate
WHERE yearid = '2014' AND datemn = '01' ) A

Rob Berendt