{"id":127,"date":"2009-07-20T14:02:00","date_gmt":"1970-01-01T00:00:00","guid":{"rendered":"http:\/\/www.yazilimmutfagi.com\/?p=127"},"modified":"2009-07-20T14:02:00","modified_gmt":"1970-01-01T00:00:00","slug":"t-sqlde-tarihler-uzerinde-islem-yapmak-icin-kullanilan-fonksiyonlar","status":"publish","type":"post","link":"https:\/\/www.yazilimmutfagi.com\/index.php\/2009\/07\/20\/t-sqlde-tarihler-uzerinde-islem-yapmak-icin-kullanilan-fonksiyonlar\/","title":{"rendered":"T-SQL’de Tarihler \u00dczerinde Islem Yapmak i\u00e7in Kullanilan Fonksiyonlar"},"content":{"rendered":"

T-SQL de Tarih islemleri için kullandigimi birçok fonksiyon vardir. Bunlardan en çok kullanilanlar bir liste halinde verdim. Tarih üzerinde islemler yapabilmek için bu kullanisli fonksiyonlari bilmemiz gerekiyor.  <\/span>Bu fonksiyonlardan bazilari tarihden tarih çikarmak, tarih içinden gün ay yil bulmak, Belirli bir tarihe gün, ay yil ekleyerek olusan tarihi bulmak gibi siralanabilir.<\/span><\/p>\n

Bu konuda çok fazla bir sey anlatmaya gerek duymuyorum. Asagidaki liste incelenecek olursa konunun anlasilacagini düsünüyorum. <\/span><\/p>\n

.<\/a><\/p>\n\n \n \n
\n

SELECT<\/span> CURRENT_TIMESTAMP<\/span>                        <\/span>— Sonuç :  <\/span>2009-07-20 10:39:15.097 <\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>day<\/span>,<\/span>2,<\/span>‘2009-07-20’<\/span>)        <\/span>     <\/span><\/span> <\/span>— Sonuç :  <\/span>2009-07-22 00:00:00.000<\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>month<\/span>,<\/span>2,<\/span>‘2009-07-20’<\/span>)<\/span>            <\/span>— Sonuç :  <\/span>2009-09-20 00:00:00.000<\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATEDIFF<\/span>(<\/span>day<\/span>,<\/span>‘2009-07-20’<\/span>,<\/span>‘2015-02-09’<\/span>)<\/span>  <\/span>— Sonuç :  <\/span>2030 (Gün olarak aldik) <\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATENAME<\/span>(<\/span>month<\/span>,<\/span> ‘2009-07-20’<\/span>)<\/span>            <\/span>— Sonuç :  <\/span>July<\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATENAME<\/span>(<\/span>weekday<\/span>,<\/span> ‘2009-07-20’<\/span>)<\/span>          <\/span>— Sonuç :  <\/span>Monday<\/span><\/span><\/p>\n

 <\/span><\/p>\n

SELECT<\/span> DATEPART<\/span>(<\/span>month<\/span>,<\/span> ‘2009-07-20’<\/span>)<\/span>            <\/span>— Sonuç :  <\/span>7<\/span><\/span><\/p>\n

 <\/span><\/p>\n

–Suanki Zaman ve Tarihi verir<\/span><\/p>\n

SELECT<\/span> GETDATE<\/span>()<\/span>                                <\/span>— Sonuç :  <\/span>2009-07-20 11:52:32.893<\/span><\/span><\/p>\n

 <\/span><\/p>\n

— Londra – Greenwich Saati<\/span><\/p>\n

SELECT<\/span> GETUTCDATE<\/span>()<\/span>                             <\/span>— Sonuç :  <\/span>2009-07-20 08:52:44.693<\/span><\/span><\/p>\n

 <\/span><\/p>\n

–Girilen string degerin tarih olup olmadigini belirler. 1 ise tarih 0 ise degil.<\/span><\/p>\n

SELECT<\/span> ISDATE<\/span>(<\/span>‘2009-07-20’<\/span>)<\/span>                     <\/span>— Sonuç :  <\/span>1<\/span><\/span><\/p>\n

SELECT<\/span> ISDATE<\/span>(<\/span>‘2014-18-09’<\/span>)<\/span>                     <\/span>— Sonuç :  <\/span>0<\/span><\/span><\/p>\n

.<\/a><\/p>\n

 <\/span><\/p>\n

–girilen Tarihin gün bilgisini verir.<\/span><\/p>\n

SELECT<\/span> DAY<\/span>(<\/span>‘2009-07-20’<\/span>)<\/span>                        <\/span>— Sonuç :  <\/span>20<\/span><\/span><\/p>\n

–girilen Tarihin ay bilgisini verir.<\/span><\/p>\n

SELECT<\/span> MONTH<\/span>(<\/span>‘2009-07-20’<\/span>)<\/span>                      <\/span>— Sonuç :  <\/span>7<\/span><\/span><\/p>\n

–Girilen Tarihin yil bilgisini verir.<\/span><\/p>\n

SELECT<\/span> YEAR<\/span>(<\/span>‘2009-07-20’<\/span>)<\/span>                       <\/span>— Sonuç :  <\/span>2009<\/span><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

–Sistem Zamanini verir.<\/span><\/p>\n

SELECT<\/span> SYSDATETIME<\/span>()<\/span>       <\/span>— Sonuç :  <\/span>2009-07-20 11:53:15.7138432<\/span><\/span><\/p>\n

 <\/span><\/p>\n

–Sistem Zamanini Greenwiche göre verir. <\/span><\/span><\/p>\n

SELECT<\/span> SYSUTCDATETIME<\/span>()<\/span>    <\/span>— Sonuç :  <\/span>2009-07-20 08:53:25.9958432<\/span><\/span><\/p>\n

 <\/span><\/p>\n

 <\/span><\/p>\n

— DATEADD: Suank, Tarihe 7 Gün ekleyelim.<\/span><\/p>\n

— Bunun için 1 hafta eklemek veya 7 gün eklemek yeterlidir.<\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>DD,<\/span> 7,<\/span> GETDATE<\/span>())<\/span><\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>WW,<\/span> 1,<\/span> GETDATE<\/span>())<\/span><\/span><\/p>\n

 <\/span><\/span><\/p>\n

— Suanki Tarihe göre Ayin hangi günündeyiz.<\/span><\/p>\n

SELECT<\/span> DAY<\/span>(<\/span>GETDATE<\/span>())<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n

— Suanki Ayin Ilk günü<\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>DD,-(<\/span>DAY<\/span>(<\/span>GETDATE<\/span>())-<\/span>1),<\/span>CONVERT<\/span>(<\/span>VARCHAR<\/span>,<\/span>GETDATE<\/span>(),<\/span>112))<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n

— Suanki Ayin son günü<\/span><\/p>\n

SELECT<\/span> DATEADD<\/span>(<\/span>DD,-(<\/span>DAY<\/span>(<\/span>DATEADD<\/span>(<\/span>MM,<\/span>1,<\/span>GETDATE<\/span>()))),<\/span>DATEADD<\/span>(<\/span>MM,<\/span>1,<\/span><\/span><\/p>\n

       <\/span>CONVERT<\/span>(<\/span>VARCHAR<\/span>,<\/span>GETDATE<\/span>(),<\/span>112)))<\/span> <\/span><\/p>\n

       <\/span><\/span><\/p>\n

— DATEDIFF: Ocak 1 2007 den bugune kadar kaç gün geçti<\/span><\/p>\n

SELECT<\/span> DATEDIFF<\/span>(<\/span>DD,<\/span>‘20070101’<\/span>,<\/span>GETDATE<\/span>())<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n

— DATENAME: Suanki Ay<\/span><\/p>\n

SELECT<\/span> DATENAME<\/span>(<\/span>MM,<\/span> GETDATE<\/span>())<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n

— Hangi Aydayiz.<\/span><\/p>\n

SELECT<\/span> MONTH<\/span>(<\/span>GETDATE<\/span>())<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n

— Hangi Yildayiz<\/span><\/p>\n

SELECT<\/span> YEAR<\/span>(<\/span>CURRENT_TIMESTAMP<\/span>)<\/span> <\/span><\/p>\n

 <\/span><\/span><\/p>\n <\/td>\n <\/tr>\n <\/tbody>\n<\/table>\n

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

T-SQL de Tarih islemleri için kullandigimi birçok fonksiyon vardir. Bunlardan en çok kullanilanlar bir liste halinde verdim. Tarih üzerinde islemler yapabilmek için bu kullanisli fonksiyonlari bilmemiz gerekiyor. Bu fonksiyonlardan bazilari tarihden tarih çikarmak ,t-sql,tsql, tarih içinden gün ay yil bulmak, <\/p>\n","protected":false},"author":517,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[422],"tags":[4317,4457,5297,5069,5662,6486,6238,5760,6037,5199,6099,6269,6457,6466,6481,6361,6398,6442,6340],"_links":{"self":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/127"}],"collection":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/users\/517"}],"replies":[{"embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/comments?post=127"}],"version-history":[{"count":0,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/posts\/127\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/media?parent=127"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/categories?post=127"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.yazilimmutfagi.com\/index.php\/wp-json\/wp\/v2\/tags?post=127"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}