mrc logo mrc logo
  • m-Power m-Power
    What is m-Power?
    Overview Demos Build Process Case Studies Specs Pricing Trial
    m-Power Resources
    Overview How-To Videos Webinars & Podcasts White Papers Fact Sheets
  • Solutions Solutions
    What does m-Power build?
    Overview Database Front-Ends Reporting CRM Systems Business Intelligence Dashboards Inventory Management Mobile Apps ERP Enhancements Modernization Spreadsheets to the web MS Access to the web B2B/Web Portals Scheduling Embedded Analytics Web Forms Workflow Data Exploration Budgeting & Forecasting APIs and Web Services Db2 Web Query Alternative
    Solutions by Industry
    Overview Manufacturing Government Foodservice Software Vendors Logistics & Supply Chain Software Consultants Healthcare
  • Services Services
    Development Services Training Mentoring
  • About About
    Overview Partners Press Releases Careers Events Contact Blog
  • Support Support
    Support Home FAQ Documentation Customer Portal Enhancements Updates Roadmap Techblog
Try m-Power

m-Power Manual

Browse:

  • Home
  • External Objects & UDFs
  • mrc Date Conversion UDFs
Back to Manual

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:

Created: October 20, 2008 | Modified: June 12, 2017

Search


Browse By Category

Build Process (13)
Starting with m-Power (8)
Retrievals (10)
Reports (15)
Summaries (4)
Maintainers (17)
Graphs (8)
m-Power Data Explorer (4)
General (24)
Calculations (5)
Utilities (9)
m-Power Administration (23)
Security (11)
Freemarker (6)
m-Painter (29)
Form Validation (5)
External Objects & UDFs (12)
Deprecated Documentation (23)
Bootstrap Templates (7)

Popular Tags

Administration Graphing Dropdowns Freemarker Record Selections Popular Calculations Bootstrap Templates Parameters Prompt Screens App Properties Excel mrc-Productivity Series Advanced Graph Properties Report Data Dictionary Production Security Build Process Bar Graphs Retrievals Email DB2 Video Database Retrieval Admin External Objects Getting Started Form Validation Application Properties Maintainer Summaries SQL Graphs Compiling Performance Java Dates m-Painter RPG Reports Tomcat Maintainers

See all tags »

michaels, ross & cole, ltd. (mrc)

Privacy Policy Cookie Policy Cookie Settings Notice at Collection Do Not Sell or Share My Personal Information

mrc (US)

2001 Midwest Road
Suite 310
Oak Brook, IL 60523
630-916-0662

mrc (UK)

Mortlake Business Centre
20 Mortlake High Street
London, SW14 8JN
+44-20-335-59566


© 2024 mrc. All rights reserved.