mrc Date Conversion UDFs

mrc supplies three date conversion routines specifically for AS/400 databases. These routines are SQL functions located in MRCAPPLLIB, and they are available for use in logical and calculated fields. All three UDFs take a numeric date as an input parameter. Function specifications are described below.

UDF Name	Description	Return Type	Input Parameters
MRC_GETDATE	Get true date from numeric date	date	1. Input date: numeric (8,0) 2. Input format: varchar(10)
MRC_GETNUMDATE	Get number field from numeric date	numeric (8,0)	1. Input date: numeric (8,0) 2. Input format: varchar (10) 3. Output format: varchar (10)
MRC_GETCHARDATE	Get character field from numeric date	varchar(50)	1. Input date: numeric (8,0) 2. Input format: varchar (10) 3. Outfmt: varchar (10)

Input Parameters

Note that the input date can be a numeric date with 8 or fewer positions.

Valid input formats

• mmddyy
• ddmmyy
• yymmdd
• cyymmdd
• mmddyyyy
• ddmmyyyy
• yyyymmdd
• yynnn
• cyynnn
• yyyynnn

Valid output formats

The output formats differ based on the function used. Since MRC_GETDATE returns a true date field, no output format is used.

For MRC_GETNUMDATE and MRC_GETCHARDATE, the following output formats are available:

Valid output formats for MRC_GETNUMDATE:

• mmddyy
• ddmmyy
• yymmdd
• mmddyyyy
• ddmmyyyy
• yyyymmdd
• mm (month number: 1-12)
• dd (day of month: 1-31)
• yy (year: 1 or 2 digits)
• yyyy (year: 4 digits)
• q (quarter: 1-4)
• jul (julian: 7 digits, yyyynnn)

Valid output formats for MRC_GETCHARDATE:

• m (month abbrev: Jan, Feb)
• mw (month name: January, February)
• d (day abbrev: Sun, Mon)
• dw (day name: Sunday, Monday)
• q (quarter: Q1, Q2)

Return values

Numeric date fields sometimes contain incorrect (non-date) values; for this reason, the UDFs will return specific values for numeric data which does not translate correctly. If the input date cannot be converted to a valid date, return values from each function will be:

mrc_getdate: 01/01/0001

mrc_getnumdate: 0

mrc_getchardate: empty string

Sample Date Conversions

Function Used	Input Date	Input Format	Output Format	Return Value
MRC_GETDATE	123199	‘mmddyy’		1999-12-31
MRC_GETDATE	10822	‘yymmdd’		2001-08-22
MRC_GETDATE	1111101	‘cyymmdd’		2011-11-01
MRC_GETDATE	08005	‘yynnn’		2008-01-05
MRC_GETNUMDATE	123199	‘mmddyy’	‘yyyymmdd’	19991231
MRC_GETNUMDATE	51210	‘ddmmyy’	‘yyyy’	2010
MRC_GETNUMDATE	51210	‘ddmmyy’	‘mmddyyyy’	12052010
MRC_GETNUMDATE	110109	‘mmddyy’	‘mm’	11
MRC_GETNUMDATE	110109	‘mmddyy’	‘q’	4
MRC_GETCHARDATE	123199	‘mmddyy’	‘m’	Dec
MRC_GETCHARDATE	123199	‘mmddyy’	‘mw’	December
MRC_GETCHARDATE	11008	‘ddmmyy’	‘d’	Wed
MRC_GETCHARDATE	11008	‘ddmmyy’	‘dw’	Wednesday
MRC_GETCHARDATE	110109	‘mmddyy’	‘q’	Q4