Schema IQUERY
date-value = CONVERT_DATE( date-string-expresssion | 6,7 or 8-position numeric expression, input-date-string-format 'YMD' )
The Convert to Date (CONVERT_DATE) function returns an SQL date
value derived from the date-string-expression. The format of that
input date-string-expression is specified as the 2nd parameter.
This function is Deterministic.
NOTE: The CVTDATE UDF is a synonym for (identical to)
CONVERT_DATE. They can be used interchangeably.
Specify the non-date value as either a character string or a
numeric expression. The length of either can be 6, 7, or 8
positions. This value is converted to a date. Most conventional
date formats are supported.
Default: 'YMD'
Identifies the format for the textual date specified on the first parameter. Most convential date formats are supported including but not limited to: MDY, YMD, DMY, ISO, EUR, JIS as well as the CL date format of CYMD and variations of: CMDY or CDMY.
dcl-s order packed(9,0); dcl-s ordDate date; dcl-s shipDate date; dcl-s iShip int(5); dcl-s iOrder int(5); EXEC SQL select ordnbr, iQuery.convert_date(orddte,'mdy'),
iQuery.convert_date(shpdte,'ymd')
INTO :order, :orddate:iOrder, :shipDate:iShip
FROM orders
WHERE custNbr = 3741
LIMIT 1;
The CONVERT_DATE UDF will return NULL when the date is invalid
(for example when the input string is blank or zero or contain
other invalid date patterns.
Using the RPG null indicator variables for SQL. as in this
example, helps reduce joblog messages and improve reliability when
coding. Simple check the iShip or iOrder variables for >= 0 and
the resulting date in SHIPDATE and ORDDATE respectively are okay.
When using SQL iQuery, the isNull() built-in function can be used
on the host variables to acomplish the same thing.
select ordnbr, iQuery.convert_date(orddte,'mdy'), iQuery.convert_date(shpdte,'ymd') INTO :order, :orddate, :shipDate FROM orders WHERE custNbr = 3741 LIMIT 1;
if (&SQLSTATE < '02000');
if isNull(&shipDate);
// Ship Date is not valid
endif;
if isNull(&ordDate);
// Order Date is not valid
endif;
endif;