Convert to Date (CONVERT_DATE) UDF

Index

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.

Parameters

date-string-expression

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.

input-date-string-format

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.

Examples

 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;