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 |