{"id":10292,"date":"2026-09-17T09:47:32","date_gmt":"2026-09-17T08:47:32","guid":{"rendered":"https:\/\/solidt.eu\/site\/?p=10292"},"modified":"2026-09-17T09:47:54","modified_gmt":"2026-09-17T08:47:54","slug":"iseries-date-conversion-function","status":"publish","type":"post","link":"https:\/\/solidt.eu\/site\/iseries-date-conversion-function\/","title":{"rendered":"iSeries Date conversion function"},"content":{"rendered":"\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">\nSELECT MyDateStrColumn, CAST(MY_TO_DATE(MyDateStrColumn) AS TIMESTAMP) FROM MyTable\n\n\n-- \/\/\/\/\/\/\/\n\nCREATE OR REPLACE FUNCTION MY_TO_DATE (\n    P_DATUM VARCHAR(100)\n)\nRETURNS DATE\nLANGUAGE SQL\nCONTAINS SQL\nNO EXTERNAL ACTION\nDETERMINISTIC\nBEGIN\n    DECLARE V1 VARCHAR(20);\n    DECLARE V2 VARCHAR(20);\n    DECLARE V3 VARCHAR(20);\n\n    DECLARE N1 INTEGER;\n    DECLARE N2 INTEGER;\n    DECLARE N3 INTEGER;\n\n    DECLARE V_DAG   INTEGER;\n    DECLARE V_MAAND INTEGER;\n    DECLARE V_JAAR  VARCHAR(4);\n\n    -- Eerste 3 numerieke waarden uit de string halen\n    SET V1 = REGEXP_SUBSTR(P_DATUM, '[0-9]+', 1, 1);\n    SET V2 = REGEXP_SUBSTR(P_DATUM, '[0-9]+', 1, 2);\n    SET V3 = REGEXP_SUBSTR(P_DATUM, '[0-9]+', 1, 3);\n\n    -- Omzetten naar getallen\n    SET N1 = TRY_CAST(V1 AS INTEGER);\n    SET N2 = TRY_CAST(V2 AS INTEGER);\n    SET N3 = TRY_CAST(V3 AS INTEGER);\n\n    -- Geen geldige 3 getallen gevonden\n    IF N1 IS NULL OR N2 IS NULL OR N3 IS NULL THEN\n        RETURN NULL;\n    END IF;\n\n    -- 3e getal is altijd het jaar\n    -- 2 cijfers = 20xx\n    IF LENGTH(V3) = 2 THEN\n        SET V_JAAR = '20' || V3;\n    ELSE\n        SET V_JAAR = V3;\n    END IF;\n\n    \/*\n       Standaard:\n           dag \/ maand \/ jaar\n\n       Als het 2e getal > 12 is:\n           maand \/ dag \/ jaar\n    *\/\n    IF N2 > 12 THEN\n        SET V_MAAND = N1;\n        SET V_DAG   = N2;\n    ELSE\n        SET V_DAG   = N1;\n        SET V_MAAND = N2;\n    END IF;\n\n    \/*\n       ISO-formaat:\n           YYYY-MM-DD\n\n       TRY_CAST zorgt ervoor dat bijvoorbeeld\n       31-02-2024 -> NULL wordt.\n    *\/\n    RETURN TRY_CAST(\n        V_JAAR\n        || '-'\n        || RIGHT('00' || DIGITS(V_MAAND), 2)\n        || '-'\n        || RIGHT('00' || DIGITS(V_DAG), 2)\n        AS DATE\n    );\nEND\n\n<\/pre>\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"inline_featured_image":false,"footnotes":""},"categories":[1],"tags":[],"class_list":["post-10292","post","type-post","status-publish","format-standard","hentry","category-uncategorized"],"_links":{"self":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/10292","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/comments?post=10292"}],"version-history":[{"count":2,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/10292\/revisions"}],"predecessor-version":[{"id":10295,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/posts\/10292\/revisions\/10295"}],"wp:attachment":[{"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/media?parent=10292"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/categories?post=10292"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/solidt.eu\/site\/wp-json\/wp\/v2\/tags?post=10292"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}