Günümüzde bazı işletmeler yapılan işlemlerin tarih ve saatlerini SQL Server tablolarında tutarken UTC (Universal Time Coordinated) formatında tutmaktadırlar. UTC, GMT (Greenwich Mean Time) ve CET (Central European Time) ile aynı anlama gelmektedir. SQL Server veritabanlarında UTC olarak tutulan bir zaman değerinin, dünyanın farklı bölgelerinden yapılacak olan sorgularda, sorgunun yapıldığı yerel bölgedeki Local Time değerine dönüştürülmesi istenebilir. Böyle bir senaryo ile karşılaşıldığında T-SQL dilinin built-in fonksiyonları akla gelebilir. DATEADD(), DATEDIFF() ve GETUTCDATE() gibi fonksiyonları iç içe kullanarak bir noktaya kadar çözüm geliştirilebilir. Ancak dünyada neredeyse bütün ülkelerde uygulanan “Yaz Saati Uygulaması” sebebiyle bu fonksiyonlardan dönecek olan değer tutarlı olmayacaktır. Örneğin ülkemizde Yaz Saati Uygulaması başlamadan UTC ile olan saat farkı +02:00 iken, Yaz Saati Uygulaması başladığında ise bu fark +03:00 olarak uygulanmaktadır. Peki hem Yaz Saati Uygulamasını dikkate alacak, hem de UTC ile olan saat farkını doğru olarak hesaplayacak hazır bir T-SQL fonksiyonu var mıdır? Heyecanlandığınızı biliyorum, ancak henüz SQL Server 2012 ile beraber böyle bir fonksiyon ne yazık ki bulunmamaktadır.
O zaman, SQL Server’da UTC Time olarak tutulan bir tarihin Local Time değerine çevrilmesi için ne yapılması gerekiyor? Bu makalemizde adım adım bu işlemi gerçekleştireceğiz.
Öncelikle bu işlemi gerçekleştirmek için SQL Server .NET CLR Integration yöntemini kullanarak SQL Server’da olmayan bir fonksiyonu C# kodu ile geliştirip SQL Server’a entegre edeceğiz ve böylelikle SQL Server özelliklerini CLR Integration ile extend etmiş olacağız. İsterseniz adım adım başlayalım:
CLR Integration Aktifleştirilmeli
C# kodu ile geliştireceğimiz bir fonksiyonun SQL Server tarafından kullanılabilmesi için, SQL Server CLR Integration özelliği aktif hale getirilmelidir. Bunun için aşağıdaki kodu çalıştırmamız gerekmektedir.
USE master
GO
sp_configure ‘clr enabled’, 1
GO
RECONFIGURE
GO
UTC Time Değrini Local Time Değerine Dönüştüren C# Kodu Yazılmalı
C# kodu ile fonksiyonumuzu oluşturacağımız için Visual Studio programını açıp yeni bir Class Library projesi açıyoruz.
Resim-1
Bir sonraki aşamada açılan sayfadaki kodların tamamını silip aşağıdaki kod bloğunu sayfamıza ekliyoruz:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
public partial class UserDefinedFunctions
{
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlDateTime ConvertToLocalTime(SqlDateTime utcTime)
{
if (utcTime.IsNull)
return utcTime;
else
return new SqlDateTime(utcTime.Value.ToLocalTime());
}
};
Bu işlemden sonra ister F6 tuşuna basarak, istersek de BUILD menüsü altında bulunan “Build Solution” tıklayarak projeyi derliyoruz. Derleme işlemi tamamlandığında yazılan kodların yanında bulunan sarı renkli gösterge yeşil renge dönecektir.
Resim-2
Oluşturulan DLL Dosyası SQL Server’ın Kullanımına Alınır
C# ile yazılan, UTC zamanı Local zamana dönüştüren “ConvertToLocalTime” fonksiyonunu barındıran proje build edilerek derlendiğinde arka tarafta .dll uzantılı bir Assembly oluşturulur. Bu dosyayı bulup SQL Server içerisindeki Assembly’lere eklenmelidir. Visual Studio ile açtığımız Class Library projesinin dosya yoluna gidilir. Kurulumdan kuruluma ve bilgisayar adına göre farklı olabileceği göz önüne alınmalıdır. Aşağıda benim dosya yolumu bulabilirsiniz:
C:\Users\Abdullah\Documents\Visual Studio 2012\Projects\UTCTimeConversion\UTCTimeConversion\bin\Debug
Bu klasör altında bulunan UTCTimeConversion (projeyi oluştururken verilen isim) adındaki dll dosyası SQL Server’a eklenecektir. SQL Server Management Studio ile ConvertToLocalTime() adındaki fonksiyonu kullanmak istediğimiz Veritabanı altında bulunan Programability sekmesindeki Assemblies klasörünün üzerinde sağ tıklayarak New Assembly seçeneğini tıklıyoruz. Açılan yeni assembly ekleme ekranında, Path To Assembly kısmında bulunan Browse butonuna tıklayarak oluşturduğumuz UTCTimeConversion.dll dosyasını ekliyoruz.
Resim-3
Oluşturulan Assembly SQL Server CLR Integration metodu ile kullanıma alınmış durumdadır.
UTC ile Local Time Arasında Dönüşüm Yapacak Olan Fonksiyon Eklenen Assembly’den Oluşturulur
Artık oluşturulan dll dosyası SQL Server kullanımına alındığı için, içerisinde bulunan ConvertToLocalTime() ismindeki fonksiyon Create edilebilir. Bunun için Assembly eklenen Database altında aşağıdaki kod çalıştırılabilir;
USE AdventureWorks2012
GO
CREATE FUNCTION ConvertToLocalTime
(@UTCTime datetime)
RETURNS DATETIME
AS
EXTERNAL NAME [UTCTimeConversion].[UserDefinedFunctions].[ConvertToLocalTime]
Bu işlemin ardından ilgili database altında (benim senaryom için AdventureWorks2012) ConvertToLocalTime() adında bir SQL CLR Integration Function oluşturulmuş olacaktır.
Oluşturduğumuz fonksiyon bir adet datetime veri tipinde parametre alacak ve aldığı bu değeri Yaz Saati Uygulamasını dikkate alarak yerel zamana çevirecektir.
Aşağıda oluşturulan fonksiyonun çalıştırılması test edilmiştir:
USE AdventureWorks2012
GO
SELECT dbo.ConvertToLocalTime(‘20140322 00:00:00’)
SELECT dbo.ConvertToLocalTime(‘20120411 00:00:00’)
SELECT dbo.ConvertToLocalTime(‘20091029 00:00:00’)
Elde edilen sonuçlar aşağıdaki gibidir:
Görüldüğü üzere ConvertToLocalTime() fonsiyonuna farklı değerler verilerek farklı yıllardaki Yaz Saati Uygulaması’nı da dikkate aldığını ve Yaz Saati Uygulamasının aktif olduğu aylarda +03:00, Yaz Saati Uygulamasının aktif olmadığı aylarda +02:00 saat eklendiği görülmektedir.
SQL Server CLR Integration kullanılarak oluşturulan C# kodundaki fonksiyonun SQL Server kullanımına alınmasını ve SQL Server’a ait bir fonksiyon gibi kullanılmasını ele almış olduk. Bu sayede UTC Time olarak tutulan zaman değerlerinin farklı ülkelerde ve Yaz Saati Uygulamasını da dikkate alarak Local Time değerinin üretilerek yerel zamanın elde edilmesini incelemiş olduk. Makale hakkında görüşlerinizi, düşüncelerinizi belirtebilirsiniz. Yorumlarınızı iletebilirsiniz. Bir sonraki makalede görüşmek üzere…
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
