{"id":207,"date":"2021-09-08T00:00:00","date_gmt":"2021-09-07T15:00:00","guid":{"rendered":"https:\/\/www.tech-tips.takmaru.com\/?p=5241"},"modified":"2021-09-08T00:00:00","modified_gmt":"2021-09-07T15:00:00","slug":"clf-date-convert-oracle","status":"publish","type":"post","link":"https:\/\/www.tech-tips.takmaru.com\/?p=207","title":{"rendered":"CLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b\u3010\u95a2\u6570\u5316\u30fbORACLE\u7de8\u3011"},"content":{"rendered":"<p>\u300c<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=135\">CLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b\u3010\u95a2\u6570\u5316\u30fbSQL Server\u7de8\u3011<\/a>\u300d\u306f\u3001SQLServer\u3067\u82e6\u5fc3\u306e\u4e0a\uff08\uff1f\uff09\u3067\u5b8c\u6210\u3057\u305f\u95a2\u6570\u3067\u3059\u304c\u3001\u540c\u3058\u3053\u3068\u3092 Oracle \u3067\u884c\u3063\u3066\u307f\u307e\u3059\u3002\u3081\u3061\u3083\u304f\u3061\u3083\u7c21\u5358\u3067\u3059\u3002<\/p>\n<h2>TO_DATE \u95a2\u6570\u3092NLS\u30d1\u30e9\u30e1\u30fc\u30bf\u6307\u5b9a\u3067\u5229\u7528\u3059\u308b<\/h2>\n<p>\u30ea\u30d5\u30a1\u30ec\u30f3\u30b9\uff08Oracle 19c\uff09\u306f\u4ee5\u4e0b\u306b\u3042\u308a\u307e\u3059\u3002<\/p>\n<p>https:\/\/docs.oracle.com\/cd\/F19136_01\/sqlrf\/TO_DATE.html#GUID-D226FA7C-F7AD-41A0-BB1D-BD8EF9440118<\/p>\n<p>\u7701\u7565\u53ef\u80fd\u306a\u306e\u3067\u666e\u901a\u306b\u4f7f\u3063\u3066\u3044\u308b\u3068\u3042\u307e\u308a\u5229\u7528\u3057\u307e\u305b\u3093\u304c\u3001\u7b2c3\u5f15\u6570\u306b\u300cnlsparam\u300d\u304c\u6e21\u305b\u307e\u3059\u3002\u3064\u307e\u308a\u3001\u4e00\u6642\u7684\u306b\u8a00\u8a9e\u8a2d\u5b9a\u3092\u5909\u66f4\u3067\u304d\u308b\u308f\u3051\u3067\u3059\u3002\u3053\u308c\u3092\u5229\u7528\u3059\u308c\u3070\u3001\u3044\u3068\u3082\u7c21\u5358\u306b\u5909\u63db\u3067\u304d\u307e\u3059\u3002\u95a2\u9023\u8a18\u4e8b\u3068\u540c\u3058\u304f\u5909\u63db\u5bfe\u8c61\u3068\u3059\u308b\u6587\u5b57\u5217\u306f\u300c<strong>02\/Sep\/2021:14:32:19<\/strong>\u300d \u3067\u3059\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT TO_DATE('02\/Sep\/2021:14:32:19','DD\/MON\/YYYY:HH24:MI:SS','NLS_DATE_LANGUAGE = English') DT FROM DUAL\n<\/code><\/pre>\n<p>SQL Developer \u3092\u4f7f\u3063\u3066\u4e0a\u8a18\u3092\u5b9f\u884c\u3057\u3066\u307f\u307e\u3059\u3002\u30a4\u30f3\u30b9\u30c8\u30fc\u30eb\u65b9\u6cd5\u7b49\u306f\u300c<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=147\">Oracle SQL Developer \u3092\u5c0e\u5165\u3059\u308b\u3010Oracle Instant Client \u7de8\u3011<\/a>\u300d\u3092\u53c2\u8003\u306b\u3057\u3066\u304f\u3060\u3055\u3044\u3002<br \/>\nSQL Developer \u306b\u3088\u308b\u65e5\u4ed8\u8868\u793a\u3067\u3059\u304c\u3001\u30c7\u30d5\u30a9\u30eb\u30c8\u304c\u3044\u307e\u3044\u3061\uff08RR-MM-DD\uff09\u306a\u306e\u3067\u300cYYYY\/MM\/DD HH24:MI:SS\u300d\u306b\u3057\u3066\u304a\u304d\u307e\u3059\u3002<\/p>\n<figure>\n<p><img decoding=\"async\" alt=\"\" src=\"https:\/\/www.tech-tips.takmaru.com\/wp-content\/uploads\/2026\/03\/2021-09-image-53-1.png\" \/><\/p><figcaption>\n<p>\u65e5\u4ed8\u66f8\u5f0f\u306e\u5909\u66f4<\/p>\n<\/figcaption><\/figure>\n<p>\u4ee5\u4e0b\u304c\u5b9f\u884c\u7d50\u679c\u3067\u3059\u3002\u3059\u3093\u306a\u308a\u3068 DATE \u578b\u306b\u5909\u63db\u3067\u304d\u3066\u3044\u307e\u3059\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT TO_DATE('02\/Sep\/2021:14:32:19','DD\/MON\/YYYY:HH24:MI:SS','NLS_DATE_LANGUAGE = English') DT FROM DUAL\n<\/code><\/pre>\n<figure>\n<p><img decoding=\"async\" alt=\"\" src=\"https:\/\/www.tech-tips.takmaru.com\/wp-content\/uploads\/2026\/03\/2021-09-image-54-1.png\" \/><\/p><figcaption>\n<p>\u5b9f\u884c\u7d50\u679c<\/p>\n<\/figcaption><\/figure>\n<h2>CLF\u65e5\u4ed8\u66f8\u5f0f\u5909\u63db\u306e\u95a2\u6570\u5316<\/h2>\n<p>\u305d\u308c\u3067\u306f\u95a2\u6570\u5316\u3057\u3066\u307f\u307e\u3059\u3002SQL Server \u306e\u3088\u3046\u306b\u82e6\u52b4\u3057\u306a\u304f\u3066\u3082\u826f\u3055\u305d\u3046\u3067\u3059\u3002<\/p>\n<pre><code class=\"language-plsql\">CREATE OR REPLACE FUNCTION TO_DATETIME(dtStr IN VARCHAR2 )\nRETURN DATE\nIS\nBEGIN\n  RETURN TO_DATE(dtStr, 'DD\/MON\/YYYY:HH24:MI:SS','NLS_DATE_LANGUAGE = English');\nEXCEPTION\n  WHEN OTHERS THEN\n    RETURN NULL;\nEND;\n<\/code><\/pre>\n<p>\u65e5\u4ed8\u578b\u3078\u5909\u63db\u306b\u5931\u6557\u3057\u305f\u5834\u5408\u306f\u3001NULL\u3092\u8fd4\u3057\u3066\u3044\u307e\u3059\u3002<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=135\">\u95a2\u9023\u8a18\u4e8b<\/a>\u3068\u540c\u3058\u30c6\u30b9\u30c8\u3092\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre><code class=\"language-sql\">WITH TEST_TABLE(DTSTR) AS (\n  SELECT '01\/Jan\/2021:09:10:25' FROM DUAL UNION ALL\n  SELECT '29\/Feb\/2020:19:59:29' FROM DUAL UNION ALL -- \u958f\u5e74\n  SELECT '01\/Mar\/2021:14:32:19' FROM DUAL UNION ALL\n  SELECT '01\/Apr\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '01\/May\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '01\/Jun\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '01\/Jul\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '31\/Aug\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '30\/Sep\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '31\/Oct\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '30\/Nov\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '31\/Dec\/2021:00:00:00' FROM DUAL UNION ALL\n  SELECT '29\/Feb\/2021:19:59:29' FROM DUAL UNION ALL -- \u958f\u5e74\u3067\u306f\u306a\u3044\uff08\u5b58\u5728\u3057\u306a\u3044\uff09\n  SELECT '30\/XXX\/2021:19:59:29' FROM DUAL           -- \u6708\u540d\u79f0\u304c\u5909\u63db\u3067\u304d\u306a\u3044\n)\nSELECT DTSTR,TO_DATETIME(DTSTR) RESULT FROM TEST_TABLE\n<\/code><\/pre>\n<figure>\n<p><img decoding=\"async\" alt=\"\" src=\"https:\/\/www.tech-tips.takmaru.com\/wp-content\/uploads\/2026\/03\/2021-09-image-56-1.png\" \/><\/p><figcaption>\n<p>\u30c6\u30b9\u30c8\u7d50\u679c<\/p>\n<\/figcaption><\/figure>\n<p>\u554f\u984c\u306a\u3055\u305d\u3046\u3067\u3059\u306d\u3002Oracle \u3060\u3068\u305a\u3044\u3076\u3093\u3068\u7c21\u5358\u306b\u3067\u304d\u307e\u3057\u305f\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u300cCLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b\u3010\u95a2\u6570\u5316\u30fbSQL Server\u7de8\u3011\u300d\u306f\u3001SQLServer\u3067\u82e6\u5fc3\u306e\u4e0a\uff08\uff1f\uff09\u3067\u5b8c\u6210\u3057\u305f\u95a2\u6570\u3067\u3059\u304c\u3001\u540c\u3058\u3053\u3068\u3092 Oracle \u3067\u884c\u3063\u3066\u307f\u307e\u3059\u3002\u3081\u3061\u3083\u304f\u3061\u3083\u7c21\u5358\u3067\u3059\u3002 TO_DATE \u95a2\u6570\u3092 [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":5240,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[6],"tags":[18,21],"class_list":["post-207","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-oracle","tag-pl-sql"],"_links":{"self":[{"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/posts\/207","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=207"}],"version-history":[{"count":0,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/posts\/207\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/media\/5240"}],"wp:attachment":[{"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=207"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=207"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=207"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}