{"id":99,"date":"2014-04-08T18:46:41","date_gmt":"2014-04-08T09:46:41","guid":{"rendered":"http:\/\/www.dbsheetclient.jp\/blog\/?p=99"},"modified":"2018-06-27T16:51:19","modified_gmt":"2018-06-27T07:51:19","slug":"sql-%e6%97%a5%e4%bb%98%e9%96%a2%e6%95%b0%e3%81%ab%e3%81%a4%e3%81%84%e3%81%a6%ef%bc%882%ef%bc%89","status":"publish","type":"post","link":"https:\/\/www.dbsheetclient.jp\/blog\/?p=99","title":{"rendered":"SQL \u65e5\u4ed8\u95a2\u6570\u306b\u3064\u3044\u3066\uff082\uff09"},"content":{"rendered":"

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

\"\u685c2__\" <\/a><\/p>\n

\u5148\u9031\u672b\u306e\u96e8\u3068\u98a8\u3067\u685c\u304c\u7d50\u69cb\u6563\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u306d\u3002\u3053\u308c\u306f\u305d\u306e\u524d\u306b\u64ae\u3063\u305f\u5bb6\u306e\u8fd1\u304f\u306e\u5e81\u820e\u306b\u3042\u308b\u685c\u306e\u5199\u771f\u3067\u3059\u3002<\/p>\n

\uff14\u6708\u306b\u5165\u308a\u3001\u5b50\u4f9b\u305f\u3061\u3082\u3044\u3088\u3044\u3088\u59cb\u696d\u5f0f\u3001\u5165\u5b66\u5f0f\u306e\u6642\u671f\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u308f\u304c\u5bb6\u3082\u5b50\u4f9b\u305f\u3061\u304c\u5b66\u6821\u3078\u884c\u304f\u6e96\u5099\u3067\u614c\u305f\u3060\u3057\u304f\u3057\u3066\u3044\u308b\u671d\u306e\u65e5\u3005\u304c\u623b\u3063\u3066\u304d\u307e\u3057\u305f\u3002<\/p>\n

\u671d\u306b\u306a\u3063\u3066\u3001\u5b50\u4f9b\u305f\u3061\u304b\u3089\u3042\u308c\u304c\u306a\u3044\u3001\u3053\u308c\u304c\u306a\u3044\u3001\u3053\u308c\u3092\u6e96\u5099\u3057\u3066\uff01\u3068\u304b\u8a00\u308f\u308c\u308b\u3068\u672c\u5f53\u306b\u5927\u5909\u3067\u3059\u3088\u306d\u3002\u524d\u306e\u65e5\u306e\u591c\u306b\u6b21\u306e\u65e5\u306e\u6e96\u5099\u3092\u3057\u3063\u304b\u308a\u3055\u305b\u308b\u3088\u3046\u306b\u8ebe\u3051\u308b\u3053\u3068\u304c\u5927\u4e8b\u3060\u3068\u3064\u304f\u3065\u304f\u611f\u3058\u307e\u3059\u3002<\/p>\n

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

\uff1c\u672c\u65e5\u306e\u984c\u6750\uff1e<\/strong> \u65e5\u4ed8\u95a2\u6570\u306b\u3064\u3044\u3066\u306e\u7d9a\u304d<\/strong><\/p>\n

\u524d\u56de\u3001\u65e5\u4ed8\u578b\u95a2\u6570\u306b\u3064\u3044\u3066\u5c11\u3057\u8a71\u3092\u3057\u307e\u3057\u305f\u304c\u3001\u65e5\u4ed8\u306e\u8a08\u7b97\u306a\u3069\u306b\u3064\u3044\u3066\u4eca\u56de\u306f\u898b\u3066\u307f\u305f\u3044\u3068\u601d\u3044\u307e\u3059\u3002<\/p>\n

\u2460\u00a0\u00a0\u00a0 \u4f55\u65e5\u5f8c\u30fb\u4f55\u65e5\u524d\u306a\u3069\u3001\u65e5\u306b\u3061\u3092\u8db3\u3057\u5f15\u304d\u3059\u308b\u5834\u5408\uff1a
<\/strong>Oracle\uff1a
<\/span>\u3000DATE\u578b\u306e\u9805\u76ee\u306b\u5bfe\u3057\u3066\u3001\u8db3\u3057\u5f15\u304d\u3057\u305f\u3044\u65e5\u6570\u3092\u5358\u7d14\u306b\u30d7\u30e9\u30b9 \u3001\u30de\u30a4\u30ca\u30b9\u3059\u308c\u3070\u3088\u3044\u3002<\/span><\/p>\n

SQL Server\uff1a
\u00a0 DATEADD\u95a2\u6570\u3000(\u69cb\u6587\uff1aDATEADD(datepart\u30d1\u30e9\u30e1\u30fc\u30bf, number, date) )\u3067\u30d1\u30e9\u30e1\u30fc\u30bf\u306b day\u3092\u4f7f\u7528\u3059\u308b\u3002<\/p>\n

\u203b\u8ffd\u8a18\uff09
Oracle\u3068\u540c\u69d8\u306b\u3001\u8db3\u3057\u5f15\u304d\u3057\u305f\u3044\u65e5\u6570\u3092\u30d7\u30e9\u30b9\u30de\u30a4\u30ca\u30b9\u3082\u53ef\u80fd\u3067\u3059\u3002\u305f\u3060\u3057\u3001Oracle\u306e\u5834\u5408\u306f\u3001\u65e5\u6570\u3060\u3051\u3067\u306a\u304f\u3001\u6642\u9593\u30fb\u5206\u30fb\u79d2\u306a\u3069\u3082 1\/24 \u30011\/24\/60\u30011\/24\/60\/60\u00a0\u306a\u3069\u3092\u8db3\u3057\u5f15\u304d\u3059\u308b\u3053\u3068\u3067\u8a08\u7b97\u304c\u53ef\u80fd\u3067\u3059\u304c\u3001SQL Server\u3067\u306f\u65e5\u6570\u307e\u3067\u3067\u3059\u3002<\/span><\/p>\n

\u4f8b\uff09\u8cfc\u5165\u65e5(DATE\u578b)\u306e7\u65e5\u5f8c\u30017\u65e5\u524d
Oracle\uff1a
<\/span>\u00a0 \u00a0 SELECT \u8cfc\u5165\u65e5
<\/span>\u3000\u3000\u3000\u3000\u3000\u3000,\u3000\u8cfc\u5165\u65e5+7\u00a0 AS \u8cfc\u5165\u65e5\u306e7\u65e5\u5f8c
<\/span>\u3000\u3000\u3000\u3000\u3000\u3000,\u3000\u8cfc\u5165\u65e5-7\u00a0 AS \u8cfc\u5165\u65e5\u306e7\u65e5\u524d
<\/span>\u00a0 \u00a0 \u00a0 FROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/span><\/p>\n

\u8cfc\u5165\u65e5\u304c \u20192014-03-25\u2019 \u3060\u3063\u305f\u5834\u5408\u3001\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308b\uff1a
\u3000\u3000\u3000\u8cfc\u5165\u65e5 \u00a0 \u00a0 \u00a0 \u00a0 \u3000\u3000\u3000\u8cfc\u5165\u65e5\u306e7\u65e5\u5f8c \u00a0 \u00a0\u8cfc\u5165\u65e5\u306e7\u65e5\u524d
<\/span>\u3000\u3000\u3000—————– \u00a0 \u00a0 \u00a0——————- \u00a0 \u00a0 ——————–
<\/span>\u3000\u3000\u300014-03-25 \u00a0 \u00a0 \u00a0 \u3000\u300014-04-01 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0\u300014-03-18<\/span><\/p>\n

SQL Server\uff1a
<\/span>\u00a0 \u00a0 SELECT\u00a0 \u8cfc\u5165\u65e5
<\/span>\u3000\u3000\u3000\u3000\u3000\u3000, \u00a0DATEADD(day, 7, \u8cfc\u5165\u65e5) \u00a0AS \u8cfc\u5165\u65e5\u306e7\u65e5\u5f8c
<\/span>\u3000\u3000\u3000\u3000\u3000\u3000,\u00a0 DATEADD(day, -7, \u8cfc\u5165\u65e5)\u00a0 AS \u8cfc\u5165\u65e5\u306e7\u65e5\u524d
<\/span>\u00a0 \u00a0 \u00a0 FROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/span><\/p>\n

\u203bOracle\u3068\u540c\u69d8\u306e\u3001+7\u3001-7 \u306a\u3069\u306e\u8a08\u7b97\u3082\u53ef\u80fd\u3067\u3059\u3002<\/p>\n

\u00a0
\u2461\u00a0\u00a0\u00a0 \u4f55\u304b\u6708\u5f8c\u30fb\u4f55\u304b\u6708\u524d\u306a\u3069\u3001\u6708\u6570\u3092\u8db3\u3057\u5f15\u304d\u3059\u308b\u5834\u5408\uff1a
<\/strong>Oracle\uff1a
ADD_MONTHS\u95a2\u6570(\u69cb\u6587\uff1aADD_MONTHS (date\u578b\u9805\u76ee, integer) )\u3002<\/span><\/p>\n

SQL Server\uff1a
<\/span>\u00a0 DATEADD\u95a2\u6570(\u69cb\u6587\uff1aDATEADD(datepart\u30d1\u30e9\u30e1\u30fc\u30bf, number, date\u578b\u9805\u76ee) )\u3067\u30d1\u30e9\u30e1\u30fc\u30bf\u306b month \u3092\u4f7f\u7528\u3059\u308b\u3002<\/span><\/p>\n

\u4f8b\uff09\u8cfc\u5165\u65e5(DATE\u578b)\u306e\u6708\u672b\u65e5\u306e\uff11\u30f6\u6708\u5f8c\u3001\uff11\u30f6\u6708\u524d
Oracle\uff1a
<\/span>\u00a0 SELECT \u00a0\u00a0LAST_DAY(\u8cfc\u5165\u65e5)\u00a0 AS \u8cfc\u5165\u65e5\u306e\u6708\u672b\u65e5
<\/span>\u3000, \u00a0LAST_DAY(ADD_MONTHS(\u8cfc\u5165\u65e5,1))\u00a0 AS \u8cfc\u5165\u65e5\u306e\u7fcc\u6708\u672b\u65e5
<\/span>\u3000, \u00a0LAST_DAY(ADD_MONTHS(\u8cfc\u5165\u65e5,-1))\u00a0 AS \u8cfc\u5165\u65e5\u306e\u524d\u6708\u672b\u65e5 <\/span>\u3000FROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/span><\/p>\n

\u00a0 \u00a0\u8cfc\u5165\u65e5\u304c \u20192014-03-25\u2019 \u3060\u3063\u305f\u5834\u5408\u3001\u7d50\u679c\u306f\u4ee5\u4e0b\u306e\u3088\u3046\u306b\u306a\u308b\uff1a
<\/span>\u3000 \u00a0 \u00a0\u8cfc\u5165\u65e5\u306e\u6708\u672b\u65e5 \u00a0 \u00a0 \u8cfc\u5165\u65e5\u306e\u7fcc\u6708\u672b\u65e5 \u00a0 \u00a0 \u8cfc\u5165\u65e5\u306e\u524d\u6708\u672b\u65e5
<\/span>\u00a0 \u00a0 \u00a0 \u00a0——————– \u00a0 \u00a0 \u00a0———————- \u00a0 \u00a0 \u00a0 \u00a0 —————–
<\/span>\u00a0 \u00a0 \u00a0 \u00a014-03-31 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a014-04-30 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a014-02-28<\/span><\/p>\n

SQL Server (2012)\uff1a
\u00a0 \u00a0 SELECT <\/span>\u00a0 \u00a0 \u00a0EOMONTH(\u8cfc\u5165\u65e5)\u00a0 AS \u8cfc\u5165\u65e5\u306e\u6708\u672b\u65e5
\u00a0 ,\u00a0 EOMONTH(DATEADD(month, 1,\u8cfc\u5165\u65e5)) \u00a0AS \u8cfc\u5165\u65e5\u306e\u7fcc\u6708\u672b\u65e5
<\/span>\u00a0 ,\u00a0 EOMONTH(DATEADD(month, -1,\u8cfc\u5165\u65e5))\u00a0 AS \u8cfc\u5165\u65e5\u306e\u524d\u6708\u672b\u65e5 <\/span>\u00a0 \u00a0 FROM \u30c6\u30fc\u30d6\u30eb\u540d;<\/span><\/span><\/p>\n

\"Excel\u6708\u672b\u65e5_result3\"<\/a><\/p>\n

\u203b\u305f\u3060\u3057\u3001\u4e0a\u8a18\u306e\u6708\u672b\u65e5\u3092\u53d6\u5f97\u3059\u308b EOMONTH\u95a2\u6570\u306f\u3001SQL Server2012\u304b\u3089\u4f7f\u3048\u308b\u6a5f\u80fd\u3067\u3059\u3002\u305d\u308c\u4ee5\u524d\u306e\u5834\u5408\u306b\u306f\u3001\u7fcc\u6708\uff11\u65e5\u304b\u3089\uff11\u65e5\u5f15\u3044\u305f\u65e5\u4ed8\u3092\u53d6\u5f97\u3059\u308b\u304b\u305f\u3061\u304c\u4e00\u822c\u7684\u3067\u3059\u306d\u3002\u3044\u308d\u3044\u308d\u306a\u66f8\u304d\u65b9\u304c\u3042\u308b\u3068\u601d\u3044\u307e\u3059\u304c\u3001\u4f8b\u3048\u3070\u4ee5\u4e0b\u306e\u3088\u3046\u306a\u6587\u3067\u4eca\u6708\u306e\u6708\u672b\u65e5\u3092\u62bd\u51fa\u3067\u304d\u307e\u3059\u3002<\/p>\n

SELECT DATEADD(DAY,-1,DATENAME(YEAR,DATEADD(MONTH,+1,GETDATE())) + ‘-‘ + DATENAME(MONTH,DATEADD(MONTH,+1,GETDATE())) + ‘-‘ + ’01’) as \u5f53\u6708\u672b<\/span><\/p>\n

\u2605\u524d\u56de\u3068\u540c\u69d8\u3001EXCEL\u304b\u3089\u30c7\u30fc\u30bf\u3092\u30a2\u30af\u30bb\u30b9\u3067\u304d\u308b\u74b0\u5883\u3067\u3042\u308c\u3070\u3001EXCEL\u306e\u307b\u3046\u3067\u6708\u672b\u65e5\u3092\u53d6\u5f97\u3059\u308b\u95a2\u6570\uff08EOMONTH\u95a2\u6570\uff09\u304c\u3042\u308b\u306e\u3067\u3001EXCEL\u5074\u3067\u6708\u672b\u65e5\u3092\u8868\u793a\u3055\u305b\u308b\u3053\u3068\u304c\u7c21\u5358\u306b\u3067\u304d\u307e\u3059\u3002<\/span><\/p>\n

\"Excel\u6708\u672b\u65e5_result2\"<\/a><\/p>\n

\u4eca\u65e5\u306f\u4ee5\u4e0a\u307e\u3067<\/p>\n

 <\/p>\n","protected":false},"excerpt":{"rendered":"

IT\u30b3\u30fc\u30c7\u30a3\u30cd\u30fc\u30bf\u306e\u30b7\u30e5\u30a6\u3067\u3059\u3002 \u5148\u9031\u672b\u306e\u96e8\u3068\u98a8\u3067\u685c\u304c\u7d50\u69cb\u6563\u3063\u3066\u3057\u307e\u3044\u307e\u3057\u305f\u306d\u3002\u3053\u308c\u306f\u305d\u306e\u524d\u306b\u64ae\u3063\u305f\u5bb6\u306e\u8fd1\u304f\u306e\u5e81\u820e\u306b\u3042\u308b\u685c\u306e\u5199\u771f\u3067\u3059\u3002 \uff14\u6708\u306b\u5165\u308a\u3001\u5b50\u4f9b\u305f\u3061\u3082\u3044\u3088\u3044\u3088\u59cb\u696d\u5f0f\u3001\u5165\u5b66\u5f0f\u306e\u6642\u671f\u306b\u306a\u308a\u307e\u3057\u305f\u3002\u308f\u304c\u5bb6\u3082\u5b50\u4f9b\u305f\u3061\u304c\u5b66 […]<\/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-99","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\/99","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=99"}],"version-history":[{"count":24,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/99\/revisions"}],"predecessor-version":[{"id":1992,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=\/wp\/v2\/posts\/99\/revisions\/1992"}],"wp:attachment":[{"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=99"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=99"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbsheetclient.jp\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=99"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}