Click here to Skip to main content
15,867,568 members
Articles / Database Development / SQL Server

CLR Function for Persian Date Converter in Microsoft SQL Server

Rate me:
Please Sign up or sign in to vote.
4.82/5 (32 votes)
26 Jul 2008CPOL3 min read 85.2K   1.4K   37   19
CLR Function for Persian Date Converter in Microsoft SQL Server

Download source - 6.45 KB

Introduction

One of the most exciting new features of Microsoft SQL Server 2005 is its ability to host .NET Common Language Runtime (CLR). This feature was not, 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 a task might include writing a date converter function to support the Persian Date inside Microsoft SQL Server. Due to the lack of support of the Persian language collation; SQL Server does not support Persian Date natively. Thankfully .NET framework 2.0 and later support PersianCalendar in the System.Globalization namespace. Now, with the facility to embed CLR functions in  Microsoft SQL Server, we can write a function to convert any DateTime format into the Persian one. In this article, I will show how easy it is to create a Persian date converter in C# and then how to embed it into the Microsoft SQL Server, and finally how to use it as a function inside the SQL Server environment.

Steps

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

image 1

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

Image 2

Next, we will see a partial class with the name 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 in SQL Server.

C#
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 passing a DateTime object as an argument, they returning the Persian Date, and Persian DateTime in the form of SqlString object

C#
[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(""); 
}

PersianCalendar class has numerous methods to extract the date parts form a DateTIme object, such as GetYear, GetMonth, and so on.

C#
[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:

C#
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 is all that we need to do inside Visual Studio. Then, we should introduce this assembly to SQL Sever. But before doing that, we should enable CLR in SQL Server, by executing the following command:

SQL
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:

SQL
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!

SQL
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.

SQL
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)


Written By
Software Developer
Canada Canada
This member has not yet provided a Biography. Assume it's interesting and varied, and probably something to do with programming.

Comments and Discussions

 
Questionپیشنهاد Pin
vahid-basirat29-Nov-16 8:03
vahid-basirat29-Nov-16 8:03 
AnswerRe: پیشنهاد Pin
OriginalGriff29-Nov-16 8:04
mveOriginalGriff29-Nov-16 8:04 
Questionhow use it in insert command Pin
agha hamid12-Feb-15 21:35
agha hamid12-Feb-15 21:35 
GeneralMy vote of 5 Pin
Member 108569816-Dec-14 23:05
Member 108569816-Dec-14 23:05 
Questionthanks Pin
Member 174567325-Oct-14 5:12
Member 174567325-Oct-14 5:12 
QuestionIs there any way when our database is SQLServer2000 Pin
mohammad majid eskandarian27-Jul-14 11:03
mohammad majid eskandarian27-Jul-14 11:03 
GeneralThanks for your teaching Pin
Adel Ameri18-Jul-14 19:19
Adel Ameri18-Jul-14 19:19 
Questionديتا تايپ در اسكيوال سرور Pin
Isaac_m4-Dec-12 4:52
Isaac_m4-Dec-12 4:52 
Questionsalam mamnun Pin
CoderMan201427-Feb-12 21:09
CoderMan201427-Feb-12 21:09 
GeneralToChristian Pin
siminal27-Jan-11 10:18
siminal27-Jan-11 10:18 
GeneralMy vote of 5 Pin
Member 448568328-Sep-10 0:00
Member 448568328-Sep-10 0:00 
GeneralGreat work Pin
Member 148265330-Jan-10 2:30
Member 148265330-Jan-10 2:30 
Generalimprove it Pin
black_phantom12-Dec-08 19:36
black_phantom12-Dec-08 19:36 
GeneralRe: improve it Pin
Ali Daneshmandi9-May-11 6:55
Ali Daneshmandi9-May-11 6:55 
The following code could be a solution for this problem:

SELECT
CASE ISNull(DateColumnName,0)
WHEN ISNull(Null,0) THEN Null
ELSE dbo.ToPersianDate(DateColumnName)
END FROM Table_Name

These lines of codes firstly checks the value of the Date column and if it finds it is Null,
it replaces null value to min date value, then by using case statement we can convert it back to null value.
If the value is not null so we can use PersianDate/PersianDateTime functions to convert the Date.
Ali Daneshmandi

GeneralThats Great Pin
Arin.Net28-Jul-08 19:04
Arin.Net28-Jul-08 19:04 
GeneralNice Job Pin
Siavash Mortazavi28-Jul-08 7:57
Siavash Mortazavi28-Jul-08 7:57 
GeneralGood Pin
Sam Farajpour Ghamari26-Jul-08 19:59
professionalSam Farajpour Ghamari26-Jul-08 19:59 
General[Message Removed] Pin
Mojtaba Vali26-Jul-08 17:45
Mojtaba Vali26-Jul-08 17:45 

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

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