{"id":1216,"date":"2008-10-20T03:45:54","date_gmt":"2008-10-20T08:45:54","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/legacy\/?page_id=1216"},"modified":"2017-06-12T14:12:44","modified_gmt":"2017-06-12T19:12:44","slug":"mrc-date-conversion-udfs","status":"publish","type":"page","link":"https:\/\/www.mrc-productivity.com\/legacy\/external-objects-udf\/mrc-date-conversion-udfs","title":{"rendered":"mrc Date Conversion UDFs"},"content":{"rendered":"

<\/p>\n

mrc Date Conversion UDFs (User-Defined Functions)<\/h1>\n

 <\/p>\n

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.<\/p>\n\n\n\n\n\n\n\n
\n

UDF Name<\/o:p><\/span><\/p>\n<\/td>\n

\n

Description<\/o:p><\/span><\/p>\n<\/td>\n

\n

Return Type<\/o:p><\/span><\/p>\n<\/td>\n

\n

Input Parameters<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETDATE<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Get true date from numeric date<\/o:p><\/span><\/p>\n<\/td>\n

\n

date<\/o:p><\/span><\/p>\n<\/td>\n

\n

1. Input date: <\/span>numeric (8,0)<\/o:p><\/span><\/p>\n

2. Input format: <\/span>varchar(10)<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

 <\/o:p><\/span><\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Get number field from numeric date<\/o:p><\/span><\/p>\n<\/td>\n

\n

numeric (8,0)<\/o:p><\/span><\/p>\n<\/td>\n

\n

1. Input date: <\/span>numeric (8,0)<\/o:p><\/span><\/p>\n

2. Input format: <\/span>varchar (10)<\/o:p><\/span><\/p>\n

3. Output format: <\/span>varchar (10)<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Get character field from numeric date<\/o:p><\/span><\/p>\n<\/td>\n

\n

varchar(50)<\/o:p><\/span><\/p>\n<\/td>\n

\n

1. Input date: <\/span>numeric (8,0)<\/o:p><\/span><\/p>\n

2. Input format: <\/span>varchar (10)<\/o:p><\/span><\/p>\n

3. Outfmt: <\/span>varchar (10)<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

Input Parameters<\/strong><\/p>\n

Note that the input date can be a numeric date with 8 or fewer positions.<\/p>\n

Valid input formats<\/strong><\/p>\n

mmddyy<\/p>\n

ddmmyy<\/p>\n

yymmdd<\/p>\n

cyymmdd<\/p>\n

mmddyyyy<\/p>\n

ddmmyyyy<\/p>\n

yyyymmdd<\/p>\n

yynnn<\/p>\n

cyynnn<\/p>\n

yyyynnn<\/p>\n

Valid output formats<\/strong><\/p>\n

The output formats differ based on the function used. Since MRC_GETDATE returns a true date field, no output format is used.<\/p>\n

For MRC_GETNUMDATE and MRC_GETCHARDATE, the following output formats are available:<\/p>\n

Valid output formats for MRC_GETNUMDATE:<\/strong><\/p>\n

mmddyy<\/p>\n

ddmmyy<\/p>\n

yymmdd<\/p>\n

mmddyyyy<\/p>\n

ddmmyyyy<\/p>\n

yyyymmdd<\/p>\n

mm (month number: 1-12)<\/p>\n

dd (day of month: 1-31)<\/p>\n

yy (year: 1 or 2 digits)<\/p>\n

yyyy (year: 4 digits)<\/p>\n

q (quarter: 1-4)<\/p>\n

jul (julian: 7 digits, yyyynnn)<\/p>\n

Valid output formats for MRC_GETCHARDATE:<\/strong><\/p>\n

m (month abbrev: Jan, Feb)<\/p>\n

mw (month name: January, February)<\/p>\n

d (day abbrev: Sun, Mon)<\/p>\n

dw (day name: Sunday, Monday)<\/p>\n

q (quarter: Q1, Q2)<\/p>\n

Return values<\/strong><\/p>\n

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:<\/p>\n

mrc_getdate: 01\/01\/0001<\/p>\n

mrc_getnumdate: 0<\/p>\n

mrc_getchardate: empty string<\/p>\n

Sample Date Conversions<\/strong><\/p>\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n\n
\n

Function Used<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Input Date<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Input Format<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Output Format<\/o:p><\/span><\/strong><\/p>\n<\/td>\n

\n

Return Value<\/o:p><\/span><\/strong><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

123199<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n

\n

1999-12-31<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

10822<\/o:p><\/span><\/p>\n<\/td>\n

\n

'yymmdd'<\/o:p><\/span><\/p>\n<\/td>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n

\n

2001-08-22<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

1111101<\/o:p><\/span><\/p>\n<\/td>\n

\n

'cyymmdd'<\/o:p><\/span><\/p>\n<\/td>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n

\n

2011-11-01<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

08005<\/o:p><\/span><\/p>\n<\/td>\n

\n

'yynnn'<\/o:p><\/span><\/p>\n<\/td>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n

\n

2008-01-05<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

123199<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'yyyymmdd'<\/o:p><\/span><\/p>\n<\/td>\n

\n

19991231<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

51210<\/o:p><\/span><\/p>\n<\/td>\n

\n

'ddmmyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'yyyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

2010<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

51210<\/o:p><\/span><\/p>\n<\/td>\n

\n

'ddmmyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyyyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

12052010<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

110109<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mm'<\/o:p><\/span><\/p>\n<\/td>\n

\n

11<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETNUMDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

110109<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'q'<\/o:p><\/span><\/p>\n<\/td>\n

\n

4<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

 <\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

123199<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'm'<\/o:p><\/span><\/p>\n<\/td>\n

\n

Dec<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

123199<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mw'<\/o:p><\/span><\/p>\n<\/td>\n

\n

December<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

11008<\/o:p><\/span><\/p>\n<\/td>\n

\n

'ddmmyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'd'<\/o:p><\/span><\/p>\n<\/td>\n

\n

Wed<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

11008<\/o:p><\/span><\/p>\n<\/td>\n

\n

'ddmmyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'dw'<\/o:p><\/span><\/p>\n<\/td>\n

\n

Wednesday<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n

\n

MRC_GETCHARDATE<\/o:p><\/span><\/p>\n<\/td>\n

\n

110109<\/o:p><\/span><\/p>\n<\/td>\n

\n

'mmddyy'<\/o:p><\/span><\/p>\n<\/td>\n

\n

'q'<\/o:p><\/span><\/p>\n<\/td>\n

\n

Q4<\/o:p><\/span><\/p>\n<\/td>\n<\/tr>\n<\/table>\n

See Create Logical Field based on Registered UDF<\/a> for more information on using UDFs in a logical field.<\/p>\n

Example: Simple Reporting Date Conversion<\/strong><\/p>\n

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<\/em>, so that the field is available whenever the table is selected for an application.<\/p>\n

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.<\/p>\n

<\/p>\n

Ensure your field name contains no spaces. Select UDF as the Field Type. Click "Accept".<\/p>\n

<\/p>\n

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.<\/p>\n

<\/p>\n

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.<\/p>\n

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.<\/p>\n

We do the same for the output format parameter, entering our desired output format of "mmddyy".<\/p>\n

Note:<\/strong> 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.<\/p>\n

Note:<\/strong> Literal constants must be surrounded by single quotes.<\/p>\n

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.<\/p>\n

Note:<\/strong> Logical fields are for display purposes only and cannot be updated in the database.<\/p>\n

Now users can simply select the logical field in their application definition. A sample retrieval using our new logical field is shown below:<\/p>\n

<\/p>\n

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:<\/p>\n

<\/p>\n

<\/p>\n","protected":false},"excerpt":{"rendered":"

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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"parent":1652,"menu_order":9,"comment_status":"closed","ping_status":"closed","template":"","meta":{"footnotes":""},"class_list":["post-1216","page","type-page","status-publish","hentry"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1216","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/comments?post=1216"}],"version-history":[{"count":6,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1216\/revisions"}],"predecessor-version":[{"id":8584,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1216\/revisions\/8584"}],"up":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/pages\/1652"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/legacy\/wp-json\/wp\/v2\/media?parent=1216"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}