کار با توابع شمسی در دیتابیس MS-SQL و CLR

اگر با دیتابیس کار می کنید حتما به گزارش هایی برخوردید که لازم باشه از تاریخ شمسی استفاده کنید.  همونطور که می دونید توابع شمسی در دیتابیس ها (دیتابیس مد نظرمون در این مقاله MS SQL!) وجود ندارند البته با روش های مختلف می شود این تبدیل رو انجام داد هر چند که در ادامه مختصری درباره هر کدام ازاین روش ها توضیح خواهم داد ولی هدف این مقاله آموزش پیاده سازی یک User-Defined Function برای تبدیل تاریخ میلادی به شمسی و استفاده آن در SQL می باشد.

روش های دیگر کار با تاریخ شمسی

• برای تبدیل تاریخ میلادی به شمسی می تونید از تابع FORMAT استفاده کنید که با پر کردن آرگومان culture با مقدار ‘fa-ir’ این تبدیل به سادگی هر چه تمام صورت می گیرد. به طور مثال:

DECLARE @today DATE = GETDATE();
SELECT FORMAT( @today, 'yyyy-MM-dd', 'fa-IR' )		-- ۱۴۰۰-۰۱-۰۵
SELECT FORMAT( @today, 'yyyy-MMM', 'fa-IR' )		--۱۴۰۰-فروردین
SELECT FORMAT( @today, 'yyyy-MM-dd-dddd', 'fa-IR' )	--۱۴۰۰-۰۱-۰۵-پنجشنبه

• روش دیگر تبدیل و کار با تاریخ شمسی، استفاده از جدول DimDate است که بسیار پر کاربرد در حوزه هوش تجاری می باشد و انعطاف زیادی برای اعمال تغییرات دارد. کار با این روشساده است و تنها کافیه جدول رو با جدول های دیگر ارتباط دهید. همچنین می تونید از قابلیت های Time intelligence هم در این روش استفاده کنید البته با کمی کدنویسی و تنظیمات!

و اما نحوه نوشتن یک تابع سفارشی در MS-SQL

برای این کار لازمه Visual Studio رو نصب داشته باشید. نوشتن تابع با استفاده از زبان #C صورت می گیرد و برای تبدیل تاریخ میلادی به شمسی از System.Globalization و کلاس PersianCalendar که از پیش توسط مایکروسافت تهیه شده استفاده می کنیم (کار رو ساده کرد!).

برای شروع، یک پروژه جدید Sql Server در محیط Visual Studio درست می کنیم. برای این کار از مسیر  File > New > Project پروژه SQL Server را جستجو کنید.

و پس از تعیین نام و مسیر ذخیره، پروژه را ایجاد کنید.

با راست کلیک بر روی پروژه، یک item جدید با عنوان User-Defined Function اضافه کنید.

و در نهایت آیتم زیر را به پروژه اضافه کنید. و دقت کنید نام JalaliFunction رو برای این فایل انتخاب کردم بعدا با این نام کار داریم.

با باز شدن فایل به غیر از کتابخانه ها کد زیر را خواهید دید.

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JalaliFunction()
    {
        // Put your code here
        return new SqlString (string.Empty);
    }
}

کدر زیر رو در داخل بلاک function و جایگزین return new SqlString (string.Empty) کنید.

string result = "";
if (dt != null)
{
    PersianCalendar objPersianCalendar = new PersianCalendar();
    int year = objPersianCalendar.GetYear(dt);
    int month = objPersianCalendar.GetMonth(dt);
    int day = objPersianCalendar.GetDayOfMonth(dt);
    result = year.ToString().PadLeft(4, '0') + "/" +
                month.ToString().PadLeft(2, '0') + "/" +
                day.ToString().PadLeft(2, '0');
}
return new SqlString(result);

همچنین داخل function مقدار پارامتر  DateTime dt رو قرار دهید و در نهایت خواهید داشت:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Globalization;

public partial class UserDefinedFunctions
{
    [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString JalaliFunction(DateTime dt)
    {
        string result = "";
        if (dt != null)
        {
            PersianCalendar objPersianCalendar = new PersianCalendar();
            int year = objPersianCalendar.GetYear(dt);
            int month = objPersianCalendar.GetMonth(dt);
            int day = objPersianCalendar.GetDayOfMonth(dt);
            result = year.ToString().PadLeft(4, '0') + "/" +
                        month.ToString().PadLeft(2, '0') + "/" +
                        day.ToString().PadLeft(2, '0');
        }
        return new SqlString(result);
    }
}

پس از راست کلیک بر روی solution و انتخاب build کار با محیط Visual Studio تمام خواهد شد.

قبل از ایجاد تابع ساخته شده در SQL لازمه برخی تنظیمات انجام بشه که با اجرا کد زیر با داشتن دسترسی لازم تغییرات اعمال میشه. البته یک بار انجام بدید کافیه خواهد بود و نیاز نیست کد زیر را برای هر پروژه های دیگه تکرار کنید.

sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE;  
GO  
sp_configure 'clr enabled', 1;  
GO
EXEC sp_configure 'clr strict security',0;
GO
RECONFIGURE;  
GO

برای ایجاد ارتباط sql با تابع نیاز است فایل آدرس dll ایجاد شده پس از build پروژه رو در مسیر کد زیر قرار بدید و اجرا کنید.

CREATE ASSEMBLY PersianSQLFunctions 
FROM 'F:\rypitech\2_PersianDateTimeCLR\PersianSQLFunctions\bin\Debug\PersianSQLFunctions.dll'

و بالاخره قدم آخر که ایجاد یه تابع در SQL است. در کد زیر نام همون اسمی که برای  فایل  استفاده کردید رو جایگزین کنید و اگر می خواهید در دیتابیس خاصی ایجاد بشه اون رو در جلوی USE قرار دهید.

USE master
GO

CREATE FUNCTION ToPersianDate 
( 
@dt DateTime 
) 
RETURNS NVARCHAR(10) 
AS EXTERNAL NAME PersianSQLFunctions.UserDefinedFunctions.JalaliFunction

و با تست تابع خواهید دید که به چه سادگی می تونید توابع خودتون رو ایجاد کنید. البته استفاده برای حجم زیاد داده این روش اصلا توصیه نمیشه!

SELECT [dbo].[ToPersianDate](GETDATE()) --1400/01/05

سخن آخر و توابع دیگر

مشابه این تابع و همچنین توابع بیشتر رو می تونید از این repository هم ببینید و ایده بگیرید (البته هیچگونه ارتباطی با من و رایپیتک نداره این صفحه github ولی کارشون جالبه). تابع اول و آخر ماه شمسی رو هم تست کنید.