{"id":1216,"date":"2008-10-20T03:45:54","date_gmt":"2008-10-20T08:45:54","guid":{"rendered":"http:\/\/www.mrc-productivity.com\/docs\/?page_id=1216"},"modified":"2023-08-28T10:59:36","modified_gmt":"2023-08-28T15:59:36","slug":"mrc-date-conversion-udfs","status":"publish","type":"ht_kb","link":"https:\/\/www.mrc-productivity.com\/docs\/knowledge-base\/mrc-date-conversion-udfs","title":{"rendered":"mrc Date Conversion UDFs"},"content":{"rendered":"\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

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

\n

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

\n

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

\n

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

MRC_GETDATE<\/span><\/strong><\/p><\/td>

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

date<\/span><\/p><\/td>

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

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

\n

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

MRC_GETNUMDATE<\/span><\/strong><\/p><\/td>

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

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

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

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

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

\n

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

MRC_GETCHARDATE<\/span><\/strong><\/p><\/td>

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

varchar(50)<\/span><\/p><\/td>

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

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

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

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

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

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

    \n
  • mmddyy<\/li>\n\n\n\n
  • ddmmyy<\/li>\n\n\n\n
  • yymmdd<\/li>\n\n\n\n
  • cyymmdd<\/li>\n\n\n\n
  • mmddyyyy<\/li>\n\n\n\n
  • ddmmyyyy<\/li>\n\n\n\n
  • yyyymmdd<\/li>\n\n\n\n
  • yynnn<\/li>\n\n\n\n
  • cyynnn<\/li>\n\n\n\n
  • yyyynnn<\/li>\n<\/ul>\n\n\n\n

    Valid output formats<\/strong><\/p>\n\n\n\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\n\n\n

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

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

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

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

        \n
      • m (month abbrev: Jan, Feb)<\/li>\n\n\n\n
      • mw (month name: January, February)<\/li>\n\n\n\n
      • d (day abbrev: Sun, Mon)<\/li>\n\n\n\n
      • dw (day name: Sunday, Monday)<\/li>\n\n\n\n
      • q (quarter: Q1, Q2)<\/li>\n<\/ul>\n\n\n\n

        Return values<\/strong><\/p>\n\n\n\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\n\n\n

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

        mrc_getnumdate: 0<\/p>\n\n\n\n

        mrc_getchardate: empty string<\/p>\n\n\n\n

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

        \n

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

        		\n

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

        		\n

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

        		\n

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

        		\n

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

        MRC_GETDATE<\/span><\/p><\/td>

        123199<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        \u00a0<\/span><\/p><\/td>

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

        MRC_GETDATE<\/span><\/p><\/td>

        10822<\/span><\/p><\/td>

        ‘yymmdd’<\/span><\/p><\/td>

        \u00a0<\/span><\/p><\/td>

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

        MRC_GETDATE<\/span><\/p><\/td>

        1111101<\/span><\/p><\/td>

        ‘cyymmdd’<\/span><\/p><\/td>

        \u00a0<\/span><\/p><\/td>

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

        MRC_GETDATE<\/span><\/p><\/td>

        08005<\/span><\/p><\/td>

        ‘yynnn’<\/span><\/p><\/td>

        \u00a0<\/span><\/p><\/td>

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

        <\/p><\/td><\/tr>

        MRC_GETNUMDATE<\/span><\/p><\/td>

        123199<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘yyyymmdd’<\/span><\/p><\/td>

        19991231<\/span><\/p><\/td><\/tr>

        MRC_GETNUMDATE<\/span><\/p><\/td>

        51210<\/span><\/p><\/td>

        ‘ddmmyy’<\/span><\/p><\/td>

        ‘yyyy’<\/span><\/p><\/td>

        2010<\/span><\/p><\/td><\/tr>

        MRC_GETNUMDATE<\/span><\/p><\/td>

        51210<\/span><\/p><\/td>

        ‘ddmmyy’<\/span><\/p><\/td>

        ‘mmddyyyy’<\/span><\/p><\/td>

        12052010<\/span><\/p><\/td><\/tr>

        MRC_GETNUMDATE<\/span><\/p><\/td>

        110109<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘mm’<\/span><\/p><\/td>

        11<\/span><\/p><\/td><\/tr>

        MRC_GETNUMDATE<\/span><\/p><\/td>

        110109<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘q’<\/span><\/p><\/td>

        4<\/span><\/p><\/td><\/tr>

        <\/p><\/td><\/tr>

        MRC_GETCHARDATE<\/span><\/p><\/td>

        123199<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘m’<\/span><\/p><\/td>

        Dec<\/span><\/p><\/td><\/tr>

        MRC_GETCHARDATE<\/span><\/p><\/td>

        123199<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘mw’<\/span><\/p><\/td>

        December<\/span><\/p><\/td><\/tr>

        MRC_GETCHARDATE<\/span><\/p><\/td>

        11008<\/span><\/p><\/td>

        ‘ddmmyy’<\/span><\/p><\/td>

        ‘d’<\/span><\/p><\/td>

        Wed<\/span><\/p><\/td><\/tr>

        MRC_GETCHARDATE<\/span><\/p><\/td>

        11008<\/span><\/p><\/td>

        ‘ddmmyy’<\/span><\/p><\/td>

        ‘dw’<\/span><\/p><\/td>

        Wednesday<\/span><\/p><\/td><\/tr>

        MRC_GETCHARDATE<\/span><\/p><\/td>

        110109<\/span><\/p><\/td>

        ‘mmddyy’<\/span><\/p><\/td>

        ‘q’<\/span><\/p><\/td>

        Q4<\/span><\/p><\/td><\/tr><\/tbody><\/table><\/figure>\n","protected":false},"excerpt":{"rendered":"

        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…<\/p>\n","protected":false},"author":1,"comment_status":"closed","ping_status":"closed","template":"","format":"standard","meta":{"footnotes":""},"ht-kb-category":[264],"ht-kb-tag":[],"class_list":["post-1216","ht_kb","type-ht_kb","status-publish","format-standard","hentry","ht_kb_category-deprecated-documentation"],"_links":{"self":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1216","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb"}],"about":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/types\/ht_kb"}],"author":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/comments?post=1216"}],"version-history":[{"count":8,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1216\/revisions"}],"predecessor-version":[{"id":13170,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb\/1216\/revisions\/13170"}],"wp:attachment":[{"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/media?parent=1216"}],"wp:term":[{"taxonomy":"ht_kb_category","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-category?post=1216"},{"taxonomy":"ht_kb_tag","embeddable":true,"href":"https:\/\/www.mrc-productivity.com\/docs\/wp-json\/wp\/v2\/ht-kb-tag?post=1216"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}