Click here to Skip to main content
Click here to Skip to main content

Creating a CLR Persian Date Convertor Function for SQL Server

, 26 Jul 2008 CPOL
Rate this:
Please Sign up or sign in to vote.
Creating a CLR Persian date convertor function for SQL Server.

Introduction

Creating a CLR Persian date convertor for SQL Server

One of the most exciting new features of MS SQL Server 2005 is its ability to host the .NET Common Language Runtime (CLR). This feature was not, however, designed merely to provide an alternative to Transact SQL (TSQL). In any development project, it is important to use the right tool for the right job. If you want to create a Stored Procedure that performs standard operations on relational data, then without doubt, TSQL is the platform to choose. Since TSQL is designed solely for the purpose of manipulating relational data, it is superb at that job. However, there are many tasks which fall outside of the realm of relational data. It is for these tasks that CLR code might be a wise choice. Such tasks might include writing a date convertor function to support Persian date inside the Microsoft SQL Server. This is due to the lack of supporting Persian collation; therefore, SQL Server does not support Persian date time. However, it seems that it should have; it is not yet affordable. We hope that Microsoft supports it in SQL Server 2008.

Anyway, thanks to the .NET framework 2.0 and later versions which have the PersianCalendar class in the System.Globalization namespace which enable us to use Persian calendar in the .NET environment. Now, with the facility to embed CLR functions in SQL Server, we can write functions to convert Georgian date time into Persian date time. In this article, I will show how easy it is to create a Persian date time convertor in C# and then embed it into SQL Server, and finally use it as a function inside the SQL Server environment.

Using the code

First of all, we need to create a SQL Server project in Visual studio.

image 1

Then, right click on PersianSQLFunctions to add a user-defined function to the project.

Next, we will see a partial class with the name of UserDefinedFunctions has been created which includes a Hello SQL function. The schema of the functions which we will create inside this class is the same as this simple function which returns a “Hello” string when we call it inside SQL Server.

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 SqlString Function1()
    {
        // Put your code here
        return new SqlString("Hello");
    }
};

Our project needs two functions, which given a DateTime object as an argument, they return the Persian date and Persian date time as a result in SqlString.

[Microsoft.SqlServer.Server.SqlFunction] 
public static SqlString ToPersianDateTime(DateTime dt) 
{ 
    return new SqlString(""); 
} 
[Microsoft.SqlServer.Server.SqlFunction] 
public static SqlString ToPersianDate(DateTime dt) 
{ 
    return new SqlString(""); 
}

The PersianCalendar class has numerous methods to extract the date parts of the receiver date times to Persian ones, such as GetYear, GetMonth, and so on.

[Microsoft.SqlServer.Server.SqlFunction] 
public static SqlString ToPersianDateTime(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); 
        int hour = objPersianCalendar.GetHour(dt); 
        int min = objPersianCalendar.GetMinute(dt); 
        int sec = objPersianCalendar.GetSecond(dt); 
        result = year.ToString().PadLeft(4, '0') + "/" +
                 month.ToString().PadLeft(2, '0') + "/" + 
        day.ToString().PadLeft(2, '0') + " " + 
        hour.ToString().PadLeft(2, '0') + ":" + 
        min.ToString().PadLeft(2, '0') + ":" + sec.ToString().PadLeft(2, '0'); 
    } 
    return new SqlString(result); 
} 
[Microsoft.SqlServer.Server.SqlFunction] 
public static SqlString ToPersianDate(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); 
}

Here is the complete code:

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 ToPersianDateTime(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); 
            int hour = objPersianCalendar.GetHour(dt); 
            int min = objPersianCalendar.GetMinute(dt); 
            int sec = objPersianCalendar.GetSecond(dt); 
            result = year.ToString().PadLeft(4, '0') + "/" +
                     month.ToString().PadLeft(2, '0') + "/" + 
                     day.ToString().PadLeft(2, '0') + " " +
                     hour.ToString().PadLeft(2, '0') + ":" + 
            min.ToString().PadLeft(2, '0') + ":" + 
                                   sec.ToString().PadLeft(2, '0'); 
        } 
        return new SqlString(result); 
    } 
    [Microsoft.SqlServer.Server.SqlFunction] 
    public static SqlString ToPersianDate(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); 
    } 
};

Finally, we need to build this class to create the PersianSQLFunctions.dll assembly. That’s all that we should do inside Visual Studio. Then, we should introduce this assembly to SQL Sever. But before that, we should enable CLR in SQL Server, by executing this command:

EXEC sp_configure 'clr enabled' , '1' 
go 
reconfigure; 

Because, CLR is disabled in SQL Server until we enable it. This procedure does the process of enabling CLR and then reconfigure the SQL Server by using the reconfigure; command. After that, we should run this command inside SQL Server:

CREATE ASSEMBLY PersianSQLFunctions 
FROM 'F:\My Projects\PersianSQLFunctions\PersianSQLFunctions
\bin\Debug\ PersianSQLFunctions.dll'

The final step for the installation of our CLR code is telling SQL Server how to match up a Transact SQL request with a CLR function. We do this with a CREATE FUNCTION statement. However, unlike the usual CREATE FUNCTION statement, there is no TSQL code. There is only the EXTERNAL NAME reference to the function. Note that the function name is fully qualified, that is, assemblyName.ClassName.FunctionName. It is also important to be aware that the EXTERNAL NAME specification is case-sensitive!

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

Note that the assembly name in the CREATE FUNCTION statement is the name you gave it when you load the assembly into SQL Server, not the name of the DLL file, which is no longer of any concern to SQL Server. The TSQL function name need not be the same as the CLR function, but it is less confusing if they are the same. My choice of character size for the NVARCHAR declarations is arbitrary; you might feel some other size is more appropriate.

The time has come to test our creation.

SELECT dbo.ToPersianDate(GETDATE()) 
‘1386/05/05’ 
SELECT dbo.ToPersianDateTime(GETDATE()) 
‘1386/05/05 18:03:24’ 

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Ali Daneshmandi
Software Developer DAN
Canada Canada
XAML Designer & Developer | Passionate about UX | Founder of www.PortraitPad.com
 
Follow me on Twitter at: @daneshmandi
My website: www.daneshmandi.com
Follow on   Twitter

Comments and Discussions

 
GeneralMy vote of 5 PinmemberMember 108569817-Dec-14 0:05 
Questionthanks PinmemberMember 174567325-Oct-14 6:12 
QuestionIs there any way when our database is SQLServer2000 Pinmembermohammad majid eskandarian27-Jul-14 12:03 
GeneralThanks for your teaching PinmemberMember 1095688418-Jul-14 20:19 
Questionديتا تايپ در اسكيوال سرور PinmemberIsaac_m4-Dec-12 5:52 
Questionsalam mamnun PinmemberCoderMan200727-Feb-12 22:09 
GeneralToChristian Pinmembersiminal27-Jan-11 11:18 
GeneralMy vote of 5 PinmemberMember 448568328-Sep-10 1:00 
GeneralGreat work PinmemberMember 148265330-Jan-10 3:30 
Thumbs Up | :thumbsup:
 
Thanke you.
 
CaptainMassoud

Generalimprove it Pinmemberblack_phantom12-Dec-08 20:36 
GeneralRe: improve it PinmemberAli Daneshmandi9-May-11 7:55 
GeneralThats Great PinmemberAMK_SDK28-Jul-08 20:04 
GeneralNice Job PinmemberSiavash Mortazavi28-Jul-08 8:57 
GeneralGood PinmemberSam Farajpour26-Jul-08 20:59 
General[Message Removed] PinmemberMojtaba Vali26-Jul-08 18:45 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.141223.1 | Last Updated 26 Jul 2008
Article Copyright 2008 by Ali Daneshmandi
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid