{"id":135,"date":"2021-09-03T00:00:00","date_gmt":"2021-09-02T15:00:00","guid":{"rendered":"https:\/\/www.tech-tips.takmaru.com\/?p=5243"},"modified":"2021-09-03T00:00:00","modified_gmt":"2021-09-02T15:00:00","slug":"clf-date-convert-sqlserver","status":"publish","type":"post","link":"https:\/\/www.tech-tips.takmaru.com\/?p=135","title":{"rendered":"CLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b\u3010\u95a2\u6570\u5316\u30fbSQL Server\u7de8\u3011"},"content":{"rendered":"<p>\u300c<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=68\">CLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b<\/a>\u300d\u3067\u6295\u7a3f\u3057\u305f\u5185\u5bb9\u3092\u305d\u306e\u307e\u307e\u95a2\u6570\u5316\u3059\u308b\u3053\u3068\u306f\u300c<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=106\">\u300c\u526f\u4f5c\u7528\u306e\u3042\u308b\u6f14\u7b97\u5b50\u3092\u95a2\u6570\u5185\u3067\u4f7f\u7528\u3059\u308b\u306e\u306f\u7121\u52b9\u300d\u3092\u56de\u907f\u3059\u308b\u3053\u3068\u306f\u3067\u304d\u306a\u3044\uff1f<\/a>\u300d\u306b\u3066\u624b\u8a70\u307e\u308a\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u7d50\u5c40\u3001\u4eca\u56de\u306e\u30c6\u30fc\u30de\u3060\u3068\u95a2\u6570\u5316\u3059\u308b\u306e\u3067\u3042\u308c\u3070\u300c\u526f\u4f5c\u7528\u306e\u3042\u308b\u6f14\u7b97\u5b50\u300d\u3092\u4f7f\u308f\u305a\u306b\u56de\u907f\u3059\u308b\u3057\u304b\u306a\u3044\u3068\u3044\u3046\u3053\u3068\u3067\u3059\u3002<br \/>\n\u3068\u3044\u3046\u3053\u3068\u3067\u4eca\u56de\u306f\u5225\u306e\u65b9\u6cd5\u3067\u95a2\u6570\u5316\u3057\u3066\u307f\u307e\u3057\u305f\u3002<br \/>\n\u95a2\u9023\u8a18\u4e8b\u3068\u540c\u3058\u304f\u5bfe\u8c61\u3068\u3059\u308b\u65e5\u4ed8\u6642\u523b\u306e\u6587\u5b57\u5217\u306f\u300c<strong>02\/Sep\/2021:14:32:19<\/strong>\u300d\u3068\u3044\u3046\u5f62\u5f0f\u3067\u3042\u308b\u3053\u3068\u3092\u524d\u63d0\u3068\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<h2>CLF\u65e5\u4ed8\u66f8\u5f0f\u5909\u63db\u306e\u95a2\u6570\u5316<\/h2>\n<p>\u5b9f\u306f\u3001LANGUAGE \u3092\u5909\u66f4\u3057\u306a\u304f\u3066\u3082\u6708\u540d\u79f0\uff08\u82f1\u7565\u79f0\uff09\u304c\u6570\u5024\u5316\u3055\u308c\u3001\u6708\u3068\u65e5\u304c\u5165\u308c\u66ff\u308f\u3063\u3066\u3044\u308c\u3070\uff08\uff1d\u300c\u65e5\/\u6708\/\u5e74\u300d\u3067\u306f\u306a\u304f\u300c\u6708\/\u65e5\/\u5e74\u300d\u3067\u3042\u308c\u3070\uff09 CONVERT \u3067\u8a8d\u8b58\u3057\u3066\u304f\u308c\u308b\u306e\u3067\u3059\u3002<\/p>\n<pre><code class=\"language-plsql\">CREATE FUNCTION TO_DATETIME(@dtStr VARCHAR(max) )\nRETURNS DATETIME\nAS\nBEGIN\n  DECLARE @dtOut DATETIME\n  DECLARE @pos1 INT = CHARINDEX('\/',@dtStr)\n  DECLARE @pos2 INT = CHARINDEX('\/',@dtStr,CHARINDEX('\/',@dtStr)+1)\n  DECLARE @dayStr VARCHAR(max) = LEFT(@dtStr, @pos1 - 1)\n  DECLARE @mntStr VARCHAR(max) = SUBSTRING(@dtStr, @pos1 + 1, @pos2 - @pos1 - 1)\n\n  SET @dtStr = STUFF(@dtStr, CHARINDEX(':',@dtStr),1,' ')\n  SET @dtStr = @mntStr + '\/' + @dayStr + '\/' + SUBSTRING(@dtStr,@pos2 + 1,LEN(@dtStr))\n\n  SELECT @dtOut = TRY_CONVERT(DATETIME,REPLACE(@dtStr,MONTH_NAME,MONTH_NUMBER))\n    FROM ( VALUES\n      (N'Jan', 1),(N'Feb', 2),(N'Mar', 3),(N'Apr',  4),(N'May',  5),(N'Jun', 6),\n      (N'Jul', 7),(N'Aug', 8),(N'Sep', 9),(N'Oct', 10),(N'Nov', 11),(N'Dec', 12)\n    ) MONTH_TABLE( MONTH_NAME,MONTH_NUMBER )\n   WHERE CHARINDEX(MONTH_NAME,@dtStr)&gt;0\n\n  RETURN @dtOut\nEND\n<\/code><\/pre>\n<p>\u300c\u65e5\u300d\u3068\u300c\u6708\u300d\u3092\u5165\u308c\u66ff\u3048\u3066\u3044\u307e\u3059\u306e\u3067\u5c11\u3057\u7169\u96d1\u306b\u898b\u3048\u308b\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002@pos1 \u306f\u6700\u521d\u306e&#8221;\/&#8221;\u306e\u4f4d\u7f6e\u3067\u3001@pos2 \u306f2\u756a\u76ee\u306e&#8221;\/&#8221;\u306e\u4f4d\u7f6e\u306b\u306a\u308a\u307e\u3059\u3002\u306a\u306e\u3067\u3001@pos1 \u307e\u3067\u304c\u300c\u65e5\u300d\u3067\u3001@pos1 \u3068 @pos2 \u306e\u9593\u304c\u300c\u6708\u300d\u3068\u3044\u3046\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002<br \/>\n\u6708\u3068\u5e74\u3092\u5165\u308c\u66ff\u3048\u3066\u3057\u307e\u3048\u3070\u3001\u300c<a href=\"http:\/\/www.tech-tips.takmaru.com\/?p=1\">\u6708\u540d\u79f0\u306e\u5909\u63db\u8868\u3092\u53d6\u5f97\u3059\u308b<\/a>\u300d\u3067\u7d39\u4ecb\u3057\u305f\u5909\u63db\u8868\u3067\u6708\u540d\u79f0\u3092\u6570\u5024\u306b\u5909\u63db\u3057\u3066\u3001\u3042\u3068\u306f CONVERT \u306b\u4efb\u305b\u307e\u3059\u3002TRY_CONVERT \u3092\u5229\u7528\u3057\u3066\u3044\u307e\u3059\u306e\u3067\u3001\u5909\u63db\u306b\u5931\u6557\u3057\u305f\u5834\u5408\u306f NULL \u304c\u8fd4\u308a\u307e\u3059\u3002\u30c6\u30b9\u30c8\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre><code class=\"language-sql\">SELECT\n   DTSTR\n , dbo.TO_DATETIME(DTSTR) RESULT\n FROM ( VALUES\n  (N'01\/Jan\/2021:09:10:25')\n ,(N'29\/Feb\/2020:19:59:29') -- \u958f\u5e74\n ,(N'01\/Mar\/2021:14:32:19')\n ,(N'01\/Apr\/2021:00:00:00')\n ,(N'01\/May\/2021:00:00:00')\n ,(N'01\/Jun\/2021:00:00:00')\n ,(N'01\/Jul\/2021:00:00:00')\n ,(N'31\/Aug\/2021:00:00:00')\n ,(N'30\/Sep\/2021:00:00:00')\n ,(N'31\/Oct\/2021:00:00:00')\n ,(N'30\/Nov\/2021:00:00:00')\n ,(N'31\/Dec\/2021:00:00:00')\n ,(N'29\/Feb\/2021:19:59:29') -- \u958f\u5e74\u3067\u306f\u306a\u3044\uff08\u5b58\u5728\u3057\u306a\u3044\uff09\n ,(N'30\/XXX\/2021:19:59:29') -- \u6708\u540d\u79f0\u304c\u5909\u63db\u3067\u304d\u306a\u3044\n) TEST_TABLE( DTSTR )\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-22-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\u3002\u4e00\u5fdc\u3001\u65e5\u4ed8\u306e<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u300cCLF \u65e5\u4ed8\u66f8\u5f0f\u3092\u65e5\u4ed8\u578b\u306b\u5909\u63db\u3059\u308b\u300d\u3067\u6295\u7a3f\u3057\u305f\u5185\u5bb9\u3092\u305d\u306e\u307e\u307e\u95a2\u6570\u5316\u3059\u308b\u3053\u3068\u306f\u300c\u300c\u526f\u4f5c\u7528\u306e\u3042\u308b\u6f14\u7b97\u5b50\u3092\u95a2\u6570\u5185\u3067\u4f7f\u7528\u3059\u308b\u306e\u306f\u7121\u52b9\u300d\u3092\u56de\u907f\u3059\u308b\u3053\u3068\u306f\u3067\u304d\u306a\u3044\uff1f\u300d\u306b\u3066\u624b\u8a70\u307e\u308a\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u7d50\u5c40\u3001\u4eca\u56de\u306e\u30c6\u30fc\u30de\u3060\u3068\u95a2\u6570\u5316\u3059\u308b\u306e\u3067\u3042 [&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":[26,29,33],"class_list":["post-135","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database","tag-sql","tag-sqlserver","tag-t-sql"],"_links":{"self":[{"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/posts\/135","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=135"}],"version-history":[{"count":0,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=\/wp\/v2\/posts\/135\/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=135"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=135"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.tech-tips.takmaru.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=135"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}