RE: Retrieve JOB (not SYSTEM) date in SQL

I see you figured it out already, but here is what came up in case it helps.
It has some extraneous error handling logic that you can ignore or chuck
altogether.
Also, I opted to use DB2SQL parameter style instead of more common GENERAL
style used by CL (old habits die hard).


CREATE FUNCTION QGPL/getJobDate()
RETURNS DATE
EXTERNAL NAME 'QGPL/GETJOBDATE'
LANGUAGE CL
PARAMETER STYLE DB2SQL
DETERMINISTIC
NO SQL
RETURNS NULL ON NULL INPUT
DBINFO
NO EXTERNAL ACTION
DISALLOW PARALLEL
NO SCRATCHPAD
NOT FENCED


/* CL code */

pgm parm(&jobDate &outind +
&sqlstate &funcname &specname &msgtext &dbinfo)

dcl &jobDate *char 10
dcl &outInd *int 2
dcl &localdate *char 6

dcl &sqlstate *char 5
dcl &funcname *char 517
dcl &specname *char 128
dcl &msgtext *char 70
dcl &dbinfo *char 816

dcl &dummy *char 2 x'0000'

monmsg (cpf0000 mch0000 sql0000) exec(goto exception)

RTVJOBA DATE(&LOCALDATE)
CVTDAT DATE(&LOCALDATE) TOVAR(&JOBDATE) TOFMT(*ISO) TOSEP(-)
chgvar &outind 0
goto exit

exception:
chgvar &outind -1
chgvar &sqlstate 'XC999'
chgvar &msgtext (&dummy *tcat 'Unexpected error occurred, +
please check the joblog')
sndpgmmsg msgid(CPF9898) msgf(QCPFMSG) +
msgdta('Unexpected exception') msgtype(*DIAG)

exit:

endpgm


HTH, Elvis

Celebrating 11-Years of SQL Performance Excellence on IBM i, i5/OS and
OS/400
www.centerfieldtechnology.com


-----Original Message-----
Subject: Re: Retrieve JOB (not SYSTEM) date in SQL

Thanks. I had coded a TOSEP(*NONE) on my CVTDAT cmd instead of TOSEP('-')
. It seems to work now.

CHGJOB DATE(010108)

STRSQL

SELECT getjobdate()
FROM SYSIBM/SYSDUMMY1

....+....1....
GETJOBDATE ( )
01/01/08

Is this OK or am I fooling myself.