وبلاگ افشار محبی

step 1: create a simple class like this in csharp

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

public class T
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static DateTime Jalali2Gregorian(string jalaliDate)
{
DateTime sqlMinDate = Convert.ToDateTime(“1/1/1753”);
DateTime res = sqlMinDate;
try
{
jalaliDate = jalaliDate.Trim();
int year = Convert.ToInt32(jalaliDate.Substring(6, 4));
int month = Convert.ToInt32(jalaliDate.Substring(3, 2));
int day = Convert.ToInt32(jalaliDate.Substring(0, 2));
PersianCalendar pc = new PersianCalendar();
res = pc.ToDateTime(year, month, day, 0, 0, 0, 0);
}
catch
{
//do nothing
}
return (DateTime)res;
}
}

Step 2: compile it using following command line:

csc.exe /t:library FirstUdf.cs

Step 3: introduce generated dll (FirstUdf.dll) to sql server and its functions using following sql script:

drop function Jalali2Gregorian
go
drop ASSEMBLY FirstUdf
go
CREATE ASSEMBLY FirstUdf FROM ‘d:FirstUdf.dll’
go
CREATE FUNCTION Jalali2Gregorian(@JalaliDate NVarchar(100)) RETURNS DateTime
AS EXTERNAL NAME FirstUdf.T.Jalali2Gregorian
go

step 4: prepare sql server 2005 to run it:

sqlscript:

USE master
GO
EXEC sp_configure ‘clr enabled’, ‘1’;
EXEC sp_configure ‘show advanced option’, ‘1’;
reconfigure

step 5: now, you have your function ready to run in SqlServer and can run it like this!:

select [dbo].[Jalali2Gregorian] (’31/03/1386′)

step 6: this simple function converts Persian date to its equal date in Gregorian