How to convert Persian date into Gregorian date in SQL Server

Question

I have some Persian dates in my sql server table with the following format:

1394/05/14

I have to use stored procedure to convert it into Gregorian date because I need to compare it with today's date.

Does anyone know the solution? I have found some codes but they have problems in leap year and such things.

BTW I have the following code in C# but I think I have to use sql server proc because this proc should be executed with a fixed schedule.

    public static DateTime ConvertToGregorianDate(string persianDate)
    {
        PersianCalendar pcalendar = new PersianCalendar();
        int Year = int.Parse(persianDate.Split('/')[0]);
        int Month = int.Parse(persianDate.Split('/')[1]);
        int Day = int.Parse(persianDate.Split('/')[2]);
        return new DateTime(Year, Month, Day, pcalendar);
    }

Thanx in advance.

Answer 1

There is a project at GitHub that exactly does what you want! You just need to install its functions in your database by following the provided instructions. Then you can convert dates like below.

The main benefit of this library is that you are totally free to shape the returned result based on your needs. In fact, there is no fixed returned format.

select dbo.JalaliToGregorian('1395/06/11','/') 
--returns 2016-09-01 00:00:00.000

select dbo.GregorianToJalali(GETDATE(),'yyyy MMMM dddd') 
-- returns 1395 پنج شنبه مهر

select dbo.GregorianToJalali(GETDATE(),'yyyy/MM/dd HH:mm')
-- returns 1395/07/01 15:04

In the above examples suppose that GETDATE() Method in Sql server returns 2016/09/22 15:04:33!