mrc Date Conversion UDFs (User-Defined Functions)

 

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

See Create Logical Field based on Registered UDF for more information on using UDFs in a logical field.

Example: Simple Reporting Date Conversion

The database field is YYYYMMDD, but the user wants to be able to display MMDDYY in all applications which use this field. We can use the Date Conversion UDF in a Logical Field, so that the field is available whenever the table is selected for an application.

To create the logical field, click the Admin tab. Then click the Registered Tables link. From here, find the table to which you want to add the converted field, and click the Fields link for that table. Next, click the Create Logical Field link.

Ensure your field name contains no spaces. Select UDF as the Field Type. Click "Accept".

Enter a field description, and select a User Defined Function from the dropdown. Since we are converting a numeric date field to another numeric format, we will select "Get Number Field from Numeric Date" as the UDF on the logical field. This UDF description corresponds to MRC_GETNUMDATE. When the UDF is selected, the screen expands to include the field length and parameter mapping fields as shown below.

We want to convert our YYYYMMDD field (named DATESHIP) to a MMDDYY field. Since we are converting a value to MMDDYY we need to change our length to 6.

m-Power will display eligible fields, based on length and data-type, to be mapped to each parameter in the "Map a Field" dropdown. Our input numeric date is DATESHIP, and our input and output formats for this UDF will be constant literals. We know the format of DATESHIP is YYYYMMDD, so we can select "Map a Constant" and enter "yyyymmdd" in the text field.

We do the same for the output format parameter, entering our desired output format of "mmddyy".

Note: If no field attributes match, you may see the text "No matching field" listed under "Map a Field". If that is the case, you must Map a Constant value.

Note: Literal constants must be surrounded by single quotes.

Click "Accept". The new logical field, SHIPDATE_MMDDYY, is now included as a field in the table definition. Whenever this table is selected for an application, this logical field will be available for selection.

Note: Logical fields are for display purposes only and cannot be updated in the database.

Now users can simply select the logical field in their application definition. A sample retrieval using our new logical field is shown below:

Since we created a numeric Logical Field, we have the option to apply an Edit Code so our new field has a Date Format. Here is a final screenshot, which utilizes the proper edit code: