{"id":594,"date":"2014-12-26T17:42:44","date_gmt":"2014-12-26T15:42:44","guid":{"rendered":"http:\/\/www.ozgurguler.net\/?p=594"},"modified":"2014-12-26T17:42:44","modified_gmt":"2014-12-26T15:42:44","slug":"microsoft-sql-serverda-yilin-ayin-ve-haftanin-ilk-ve-son-gunleri","status":"publish","type":"post","link":"https:\/\/www.ozgurguler.net\/blog\/microsoft-sql-serverda-yilin-ayin-ve-haftanin-ilk-ve-son-gunleri\/","title":{"rendered":"Microsoft SQL Server\u2019da Y\u0131l\u0131n, Ay\u0131n ve Haftan\u0131n \u0130lk ve Son G\u00fcnleri"},"content":{"rendered":"<p>Microsoft SQL Server\u2019da tarih i\u015flemlerinin \u00e7ok s\u0131k yap\u0131lmas\u0131ndan ve de\u011fi\u015fik bi\u00e7imlere ihtiya\u00e7 duyulmas\u0131ndan dolay\u0131 SQL Server \u00e7ok \u00e7e\u015fitli tarih fonksiyonlar\u0131na i\u00e7inde bar\u0131nd\u0131r\u0131r. \u00c7o\u011fu uzman kullan\u0131c\u0131da bunlar\u0131 bilir ve kullan\u0131r. Ama \u00f6yle zaman bir gelir ki \u00a0var olan tarih fonksiyonlar\u0131 istedi\u011fimiz sonu\u00e7 i\u00e7in yetersiz kalabilir. Bu y\u00fczden ihtiya\u00e7 duyulan tarih de\u011ferlerinin yada bi\u00e7imlerinin elde edilmesi i\u00e7in farkl\u0131 y\u00f6ntemler kullan\u0131labiliriz. Hayal g\u00fcc\u00fcn\u00fcze ve ihtiyac\u0131n\u0131za g\u00f6re sizlerde bu \u00f6rneklerden yola \u00e7\u0131karak \u00e7\u00f6z\u00fcmler \u00fcretebilirsiniz.<\/p>\n<p><!--more--><\/p>\n<p>\u00d6rne\u011fin ge\u00e7en kar\u015f\u0131la\u015ft\u0131\u011f\u0131m bir uygulamadan i\u00e7inde bulundu\u011fumuz ay\u0131n ilk g\u00fcn\u00fc a\u015fa\u011f\u0131daki gibi hesaplanm\u0131\u015ft\u0131(!).<\/p>\n<p>SELECT \u201901\u2019+CONVERT(VARCHAR,datepart(MONTH,GETDATE()))+CONVERT(VARCHAR,datepart(YEAR,GETDATE())) TARIH<\/p>\n<p>Bu y\u00fczden s\u0131k ihtiya\u00e7 duyulan ay\u0131n ilk g\u00fcn\u00fc, ay\u0131n son g\u00fcn\u00fc, sonraki ay\u0131n ilk g\u00fcn\u00fc veya \u00f6nceki ay\u0131n son g\u00fcn\u00fc de\u011ferleri nas\u0131l elde edebilece\u011fimize de\u011finelim istedim.<\/p>\n<p>Asl\u0131nda basit bir y\u00f6ntem kullanaca\u011f\u0131z. \u00d6rne\u011fin bug\u00fcn\u00fcn 22.05.2013 oldu\u011funu d\u00fc\u015f\u00fcn\u00fcrsek ay\u0131n ilk g\u00fcn\u00fc bulmak i\u00e7in \u00f6ncelikle bu tarihin ay\u0131n ka\u00e7\u0131nc\u0131 g\u00fcn\u00fc oldu\u011fu DAY fonksiyonu ile bulup daha sonra \u00e7\u0131kan g\u00fcn kadar eksi g\u00fcn say\u0131s\u0131n\u0131 elimizdeki tarih de\u011ferine eklersek ay\u0131n ilk g\u00fcn\u00fcn\u00fc bulmu\u015f oluruz. \u015eimdi bug\u00fcn\u00fcn tarihini baz alarak \u00f6rneklerimizi yapal\u0131m.<\/p>\n<pre>DECLARE @tarih DATETIME=GETDATE()\r\nSELECT 'Onceki Ay\u0131n Son Gunu' Aciklama,CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@tarih)),@tarih),112)  Tarih\r\nUNION ALL\r\nSELECT 'Ay\u0131n \u0130lk G\u00fcn\u00fc',CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(@tarih)-1),@tarih),112) AS Date_Value\r\nUNION ALL\r\nSELECT 'Bugunun Tarihi',CONVERT(VARCHAR(10),@tarih,112) AS Date_Value\r\nUNION ALL\r\nSELECT 'Ay\u0131n Son G\u00fcn\u00fc',CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@tarih))),DATEADD(mm,1,@tarih)),112) \r\nUNION ALL\r\nSELECT 'Sonraki Ay\u0131n \u0130lk G\u00fcn\u00fc',CONVERT(VARCHAR(10),DATEADD(dd,-(DAY(DATEADD(mm,1,@tarih))-1),DATEADD(mm,1,@tarih)),112) \r\nUNION ALL\r\nSELECT 'Haftan\u0131n \u0130lk G\u00fcn\u00fc', DATEADD(ww, DATEDIFF(ww,0,GETDATE()), 0)\r\nUNION ALL\r\nSELECT 'Sonraki Haftan\u0131n \u0130lk G\u00fcn\u00fc', DATEADD(ww, DATEDIFF(ww,0,GETDATE())+1, 0)\r\nUNION ALL\r\nSELECT 'Y\u0131l\u0131n \u0130lk G\u00fcn\u00fc',DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)\r\nUNION ALL\r\nSELECT 'Y\u0131l\u0131n Son G\u00fcn\u00fc',DATEADD(dd,-1,DATEADD(yy,0,DATEADD(yy,DATEDIFF(yy,0,getdate())+1,0)))<\/pre>\n<p>Sonu\u00e7;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"alignnone\" src=\"http:\/\/netsisnedir.files.wordpress.com\/2013\/05\/2013-05-22_095447.jpg?w=595\" alt=\"\" width=\"325\" height=\"200\" \/><\/p>\n","protected":false},"excerpt":{"rendered":"<p>Microsoft SQL Server\u2019da tarih i\u015flemlerinin \u00e7ok s\u0131k yap\u0131lmas\u0131ndan ve de\u011fi\u015fik bi\u00e7imlere ihtiya\u00e7 duyulmas\u0131ndan dolay\u0131 SQL Server \u00e7ok \u00e7e\u015fitli tarih fonksiyonlar\u0131na i\u00e7inde bar\u0131nd\u0131r\u0131r. \u00c7o\u011fu uzman kullan\u0131c\u0131da bunlar\u0131 bilir ve kullan\u0131r. Ama \u00f6yle zaman bir gelir ki \u00a0var olan tarih fonksiyonlar\u0131 istedi\u011fimiz sonu\u00e7 i\u00e7in yetersiz kalabilir. Bu y\u00fczden ihtiya\u00e7 duyulan tarih de\u011ferlerinin yada bi\u00e7imlerinin elde edilmesi i\u00e7in [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[10],"tags":[],"class_list":["post-594","post","type-post","status-publish","format-standard","hentry","category-sql-server-tsql"],"_links":{"self":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/594","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/comments?post=594"}],"version-history":[{"count":1,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/594\/revisions"}],"predecessor-version":[{"id":595,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/posts\/594\/revisions\/595"}],"wp:attachment":[{"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/media?parent=594"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/categories?post=594"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.ozgurguler.net\/blog\/wp-json\/wp\/v2\/tags?post=594"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}