{"id":133,"date":"2014-04-23T18:44:08","date_gmt":"2014-04-23T09:44:08","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=133"},"modified":"2018-06-27T16:50:54","modified_gmt":"2018-06-27T07:50:54","slug":"sql%e6%97%a5%e4%bb%98%e9%96%a2%e6%95%b0%ef%bc%88%e5%b9%b4%e9%bd%a2%e8%a8%88%e7%ae%97%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=133","title":{"rendered":"SQL\u65e5\u4ed8\u95a2\u6570\uff08\u5e74\u9f62\u8a08\u7b97\uff09"},"content":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002
\n\"untitled\"<\/a><\/p>\n

\u6625\u5148\u306b\u4eac\u90fd\u306b\u884c\u3063\u305f\u6642\u306e\u91d1\u95a3\u5bfa\u306e\u5199\u771f\u3067\u3059\u3002\u305b\u3063\u304b\u304f\u64ae\u3063\u3066\u3044\u305f\u306e\u3067\u30a2\u30c3\u30d7\u3057\u307e\u3059\u3002<\/p>\n

\u91d1\u95a3\u5bfa\u306f\u6b63\u5f0f\u306b\u306f\u9e7f\u82d1\u5bfa\u3068\u3044\u3044\u3001\u5199\u771f\u306e\u91d1\u95a3\uff08\u820e\u5229\u6bbf\uff09\u3092\u542b\u3080\u9e7f\u82d1\u5bfa\u304c\u4e16\u754c\u6587\u5316\u907a\u7523\u3068\u3057\u3066\u767b\u9332\u3055\u308c\u3066\u3044\u307e\u3059\u3002\u73fe\u5728\u306e\u91d1\u95a3\u306f\u3001\u4e09\u5cf6\u7531\u7d00\u592b\u306e\u5c0f\u8aac\u91d1\u95a3\u5bfa\u306b\u3082\u3042\u308b\u3088\u3046\u306b\u30011950\u5e74\u306e\u5931\u706b\u3067\u56fd\u5b9d\u3067\u3042\u3063\u305f\u5f53\u6642\u306e\u91d1\u95a3\u304c\u5168\u713c\u3057\u305f\u5f8c\u30011955\u5e74\u306b\u518d\u5efa\u3055\u308c\u305f\u3082\u306e\u3067\u3059\u3002
\n\u518d\u5efa\u306b\u3042\u305f\u3063\u3066\u306f\u713c\u5931\u76f4\u524d\u306e\u59ff\u3067\u306f\u306a\u304f\u5275\u5efa\u6642\u306e\u59ff\u3092\u518d\u73fe\u3059\u308b\u3068\u306e\u65b9\u91dd\u304c\u63a1\u3089\u308c\u3001\u73fe\u5728\u306e\u3088\u3046\u306a\u91d1\u8272\u306b\u5149\u308b\u8c6a\u83ef\u306a\u304b\u305f\u3061\u306b\u306a\u3063\u305f\u3068\u306e\u3053\u3068\u3002
\n\u305f\u3060\u3001\u56fd\u5b9d\u3084\u91cd\u8981\u6587\u5316\u8ca1\u304c\u3053\u306e\u3088\u3046\u306a\u4eba\u70ba\u7684\u306a\u706b\u707d\u3084\u3001\u707d\u5bb3\u306a\u3069\u3067\u640d\u5931\u3057\u3066\u3057\u307e\u3046\u3053\u3068\u306f\u672c\u5f53\u306b\u6b8b\u5ff5\u3067\u3059\u3002\u6587\u90e8\u79d1\u5b66\u7701\u3067\u3082\u3053\u306e\u3088\u3046\u306a\u6587\u5316\u8ca1\u306e\u7dcf\u5408\u9632\u707d\u5bfe\u7b56\u306a\u3069\u30ea\u30b9\u30af\u7ba1\u7406\u306b\u3064\u3044\u3066\u306f\u3044\u308d\u3044\u308d\u3068\u691c\u8a0e\u30fb\u5bfe\u7b56\u3092\u53d6\u3089\u308c\u3066\u3044\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n

\u5b9f\u306f\u3001\u6700\u8fd1\u4ed5\u4e8b\u3082\u5fd9\u3057\u304f\u306a\u308a\u3001\u5c11\u3005\u75b2\u308c\u6c17\u5473\u3002\u4e45\u3057\u3076\u308a\u306e\u6295\u7a3f\u3067\u3059\u3002
\n\u4f53\u8abf\u7ba1\u7406\u3082\u542b\u3081\u3001\u30ea\u30b9\u30af\u7ba1\u7406\u306f\u672c\u5f53\u306b\u91cd\u8981\u3067\u3059\u306d\uff01<\/p>\n

\"it-coordinator\u2460-1\"<\/a><\/p>\n

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e
\n<\/b>\u65e5\u4ed8\u95a2\u6570\u306b\u3064\u3044\u3066\u306e\u7d9a\u304d\uff08\u5e74\u9f62\u8a08\u7b97\uff09<\/strong><\/p>\n

\u30b7\u30b9\u30c6\u30e0\u3067\u306f\u3001\u751f\u5e74\u6708\u65e5\u3092\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u306e\u9805\u76ee\u306b\u6301\u3064\u30c6\u30fc\u30d6\u30eb\u304c\u5b58\u5728\u3059\u308b\u30b1\u30fc\u30b9\u304c\u975e\u5e38\u306b\u591a\u3044\u3067\u3059\u306d\u3002\u65e5\u4ed8\u306e\u8a08\u7b97\u306b\u95a2\u9023\u3057\u3066\u3001\u4eca\u56de\u306f\u5e74\u9f62\u306e\u8a08\u7b97\u306b\u3064\u3044\u3066\u898b\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u304cOracle\u3067\u3042\u308c\u3001SQL Server \u3067\u3042\u308c\u3001\u5e74\u9f62\u8a08\u7b97\u306b\u304a\u3044\u3066\u306f\u3001\u4ee5\u4e0b\u306e2\u3064\u306e\u5185\u5bb9\u3092\u78ba\u8a8d\u3059\u308b\u3053\u3068\u306b\u306a\u308b\u304b\u3068\u601d\u3044\u307e\u3059\u3002
\n\uff11\uff09\u751f\u5e74\u6708\u65e5\u3068\u57fa\u6e96\u65e5(\u901a\u5e38\u306f\u4eca\u65e5)\u306e\u300c\u5e74\u300d\u306e\u5dee
\n\uff12\uff09\u751f\u5e74\u6708\u65e5\u3068\u57fa\u6e96\u65e5(\u901a\u5e38\u306f\u4eca\u65e5)\u306e\u300c\u6708\u65e5\u300d\u306e\u5927\u5c0f\u3092\u6bd4\u8f03\u3057\u3001\u57fa\u6e96\u65e5\u304c\u8a95\u751f\u65e5\u306b\u9054\u3057\u3066\u306a\u3051\u308c\u3070 -1<\/p>\n

SQL\u3068\u3057\u3066\u306f\u3044\u308d\u3044\u308d\u306a\u3084\u308a\u65b9\u304c\u3042\u308b\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u4ee5\u4e0b\u306b\u4f8b\u3092\u6319\u3052\u307e\u3059\u3002<\/p>\n

Oracle\uff1a<\/strong>
\nSELECT
\nEXTRACT(YEAR FROM \u57fa\u6e96\u65e5) – EXTRACT(YEAR FROM \u8a95\u751f\u65e5) -CASE WHEN TO_CHAR(\u57fa\u6e96\u65e5, ‘MMDD’) < TO_CHAR(\u8a95\u751f\u65e5, ‘MMDD’) THEN 1 ELSE 0 END AS \u5e74\u9f62
\nFROM \u30c6\u30fc\u30d6\u30eb\u540d ;<\/p>\n

SQL Server\uff1a<\/strong>
\nSELECT YEAR(\u57fa\u6e96\u65e5) – YEAR(\u751f\u5e74\u6708\u65e5)-
\nCASE WHEN RIGHT(CONVERT(VARCHAR , \u57fa\u6e96\u65e5, 112), 4) < RIGHT(CONVERT(VARCHAR , \u751f\u5e74\u6708\u65e5, 112), 4) THEN 1 ELSE 0 END AS \u5e74\u9f62
\nFROM \u30c6\u30fc\u30d6\u30eb\u540d ;<\/p>\n

\u203bYEAR(\u57fa\u6e96\u65e5) – YEAR(\u751f\u5e74\u6708\u65e5) \u306f\u3001DATEDIFF(YEAR,\u751f\u5e74\u6708\u65e5,GETDATE())\u3067\u3082\u540c\u3058<\/p>\n

\u4f8b\uff09
\nSELECT \u793e\u54e1\u30b3\u30fc\u30c9
\n, \u7d9a\u67c4
\n, CONVERT(VARCHAR,\u751f\u5e74\u6708\u65e5,111) AS \u751f\u5e74\u6708\u65e5
\n, YEAR(GETDATE()) – YEAR(\u751f\u5e74\u6708\u65e5)-
\nCASE WHEN RIGHT(CONVERT(VARCHAR , getdate(), 112), 4) < RIGHT(CONVERT(VARCHAR , \u751f\u5e74\u6708\u65e5, 112), 4) THEN 1 ELSE 0 END AS \u5e74\u9f62
\nFROM dbo.D\u793e\u54e1\u5bb6\u65cf
\nORDER BY \u793e\u54e1\u30b3\u30fc\u30c9, \u751f\u5e74\u6708\u65e5;<\/p>\n

\"\u5e74\u9f62\u7d50\u679c2_result\"
\n<\/a><\/p>\n

\u307e\u305f\u3001\u7c21\u5358\u306b\u8a08\u7b97\u3059\u308b\u65b9\u6cd5\u3068\u3057\u3066\u3001\u65e5\u4ed8\u578b(yyyymmdd)\u30928\u6841\u6570\u5024\u306b\u5909\u63db\u5f8c\u306b10000\u3067\u5272\u3063\u3066\u5c0f\u6570\u70b9\u3092\u5207\u308a\u6368\u3066\u308b\u65b9\u6cd5\u3082\u3088\u304f\u4f7f\u308f\u308c\u308b\u3088\u3046\u3067\u3059\u3002<\/p>\n

Oracle\uff1a<\/strong>
\nSELECT TRUNC((TO_CHAR(\u57fa\u6e96\u65e5,’YYYYMMDD’) – TO_CHAR(\u751f\u5e74\u6708\u65e5, ‘YYYYMMDD’)) \/10000, 0) AS \u5e74\u9f62 FROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/p>\n

\u203bTRUNC\u95a2\u6570\u306f\u5207\u308a\u6368\u3066\u3067\u3001\u7b2c2\u5f15\u6570\u304c\u5c11\u6570\u7b2c\u4f55\u4f4d\u3067\u306e\u5207\u308a\u6368\u3066\u304b\u3092\u793a\u3059\u3002<\/p>\n

SQL Server\uff1a<\/strong>
\nSELECT
\nROUND((CONVERT(INT,CONVERT(VARCHAR(8),\u57fa\u6e96\u65e5,112))-CONVERT(INT,CONVERT(VARCHAR(8),\u751f\u5e74\u6708\u65e5,112))\/10000,0,1) AS \u5e74\u9f62
\nFROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/p>\n

\u203bROUND\u95a2\u6570\u306e\u7b2c3\u5f15\u6570\u304c0\u4ee5\u5916\u306e\u5834\u5408\u306f\u5207\u308a\u6368\u3066\u306b\u306a\u308a\u307e\u3059\u3002\u307e\u305f\u3001FLOOR\u95a2\u6570\u3092\u4f7f\u7528\u3059\u308b\u3068\u3001\u5f15\u6570\u306b\u5bfe\u3057\u3066\u3001\u305d\u308c\u4ee5\u4e0b\u3067\u6700\u3082\u5927\u304d\u3044\u6574\u6570\u5024\u3092\u53d6\u308b\u306e\u3067\u540c\u3058\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002
\n\u203b\u958f\u5e74\u751f\u307e\u308c\u306e\u65b9\u3082\u30013\u67081\u65e5\u6642\u70b9\u3067\u304d\u3061\u3093\u3068\u5e74\u9f62\u304c\u4e0a\u304c\u308a\u307e\u3059\u3002<\/p>\n

\u4f8b\uff09
\nSELECT \u793e\u54e1\u30b3\u30fc\u30c9
\n, \u7d9a\u67c4
\n, CONVERT(VARCHAR,\u751f\u5e74\u6708\u65e5,111) AS \u751f\u5e74\u6708\u65e5
\n, ROUND((CONVERT(INT,CONVERT(VARCHAR,getdate(),112))-CONVERT(INT,CONVERT(VARCHAR,\u751f\u5e74\u6708\u65e5,112)))\/10000,0,1) AS \u5e74\u9f62
\nFROM dbo.D\u793e\u54e1\u5bb6\u65cf
\nORDER BY \u793e\u54e1\u30b3\u30fc\u30c9, \u751f\u5e74\u6708\u65e5;<\/p>\n

\u7d50\u679c\u306f\u6700\u521d\u306e\u51e6\u7406\u3068\u540c\u3058\u3002<\/p>\n

\u6ce8\u610f\uff09\u65e5\u672c\u306e\u6cd5\u5f8b\uff08\u5e74\u9f62\u8a08\u7b97\u306b\u95a2\u3059\u308b\u6cd5\u5f8b\uff09\u4e0a\u306f\u3001\u5e74\u9f62\u3068\u3044\u3046\u306e\u306f\u8a95\u751f\u65e5\u306e\u524d\u65e5\u3067\u52a0\u7b97\u3055\u308c\u308b\u3082\u306e\u306e\u305f\u3081\u3001\u8a08\u7b97\u7d50\u679c\u306e\u5229\u7528\u76ee\u7684\u306b\u3088\u3063\u3066\u306f\u4e0a\u8a18\u306e\u3088\u3046\u306a\u8a08\u7b97\u306b1\u65e5\u52a0\u7b97\u3059\u308b\u306a\u3069\u8abf\u6574\u304c\u5fc5\u8981\u306b\u306a\u308a\u307e\u3059\u3002\u5e74\u9f62\u3092\u5143\u306b\u4f55\u3089\u304b\u306e\u8a08\u7b97\u3059\u308b\u3088\u3046\u306a\u5834\u5408\u3001\u5e74\u9f62\u8a08\u7b97\u3092\u6cd5\u5f8b\u306b\u5247\u3063\u3066\u884c\u3046\u306e\u304b\u3001\u4e00\u822c\u6163\u7fd2\u306b\u57fa\u3065\u3044\u3066\u884c\u3063\u3066\u3044\u3044\u306e\u304b\u3088\u304f\u8abf\u67fb\u3057\u3066\u304b\u3089\u8a08\u7b97\u5f0f\u3092\u6c7a\u5b9a\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n

\u2605\u307e\u305f\u3001EXCEL<\/strong>\u304b\u3089\u30c7\u30fc\u30bf\u3092\u30a2\u30af\u30bb\u30b9\u3067\u304d\u308b\u74b0\u5883\u3067\u3042\u308c\u3070\u3001EXCEL\u306e\u307b\u3046\u3067\u5e74\u9f62\u8a08\u7b97\u3092\u3067\u304d\u308b\uff08DATEDIF\u95a2\u6570\uff09\u306e\u3067\u3001EXCEL\u5074\u3067\u5e74\u9f62\u3092\u8868\u793a\u3055\u305b\u308b\u3053\u3068\u304c\u7c21\u5358\u306b\u3067\u304d\u307e\u3059\u3002
\n\"\u5e74\u9f62\u7d50\u679cExcel_result\"<\/a>
\n\u3010\u66f8\u5f0f\u3011
\nDATEDIF(\u958b\u59cb\u65e5,\u7d42\u4e86\u65e5,\u5358\u4f4d)
\n\u3010\u5358\u4f4d\u306e\u5f15\u6570\u3011
\n“Y” \u671f\u9593\u5185\u306e\u6e80\u5e74\u6570
\n“M” \u671f\u9593\u5185\u306e\u6e80\u6708\u6570
\n“D” \u671f\u9593\u5185\u306e\u65e5\u6570
\n“MD” \u958b\u59cb\u65e5\u304b\u3089\u7d42\u4e86\u65e5\u307e\u3067\u306e\u65e5\u6570
\n“YM” \u958b\u59cb\u65e5\u304b\u3089\u7d42\u4e86\u65e5\u307e\u3067\u306e\u6708\u6570
\n“YD” \u958b\u59cb\u65e5\u304b\u3089\u7d42\u4e86\u65e5\u307e\u3067\u306e\u65e5\u6570<\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u6625\u5148\u306b\u4eac\u90fd\u306b\u884c\u3063\u305f\u6642\u306e\u91d1\u95a3\u5bfa\u306e\u5199\u771f\u3067\u3059\u3002\u305b\u3063\u304b\u304f\u64ae\u3063\u3066\u3044\u305f\u306e\u3067\u30a2\u30c3\u30d7\u3057\u307e\u3059\u3002 \u91d1\u95a3\u5bfa\u306f\u6b63\u5f0f\u306b\u306f\u9e7f\u82d1\u5bfa\u3068\u3044\u3044\u3001\u5199\u771f\u306e\u91d1\u95a3\uff08\u820e\u5229\u6bbf\uff09\u3092\u542b\u3080\u9e7f\u82d1\u5bfa\u304c\u4e16\u754c\u6587\u5316\u907a\u7523\u3068\u3057\u3066\u767b\u9332\u3055\u308c\u3066\u3044\u307e\u3059\u3002\u73fe\u5728\u306e\u91d1 […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[],"class_list":["post-133","post","type-post","status-publish","format-standard","hentry","category-5"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/133","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=133"}],"version-history":[{"count":7,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/133\/revisions"}],"predecessor-version":[{"id":1991,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/133\/revisions\/1991"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=133"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=133"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=133"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}