CODEWITHSUNDEEP
c#sql-serverprocedure
Payam Sh
Payam Sh

Reputation: 601

How to convert Persian date into Gregorian date in SQL Server

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.

Upvotes: 5

Views: 11863

Answers (4)

Farshid Shekari
Farshid Shekari

Reputation: 2449

You can use bellow functions for your purpose (Iranian Calendar to Georgian Calendar) and for more information you can see here:

-- First, we need to convert Persian calendar date to Julian Calendar date
Create FUNCTION [dbo].[UDF_Persian_To_Julian](@iYear int,@iMonth int,@iDay int)
RETURNS bigint
AS
Begin
 
Declare @PERSIAN_EPOCH  as int
Declare @epbase as bigint
Declare @epyear as bigint
Declare @mdays as bigint
Declare @Jofst  as Numeric(18,2)
Declare @jdn bigint
 
Set @PERSIAN_EPOCH=1948321
Set @Jofst=2415020.5
 
If @iYear>=0 
    Begin
        Set @epbase=@iyear-474 
    End
Else
    Begin
        Set @epbase = @iYear - 473 
    End
    set @epyear=474 + (@epbase%2820) 
If @iMonth<=7
    Begin
        Set @mdays=(Convert(bigint,(@iMonth) - 1) * 31)
    End
Else
    Begin
        Set @mdays=(Convert(bigint,(@iMonth) - 1) * 30+6)
    End
    Set @jdn =Convert(int,@iday) + @mdays+ Cast(((@epyear * 682) - 110) / 2816 as int)  + (@epyear - 1) * 365 + Cast(@epbase / 2820 as int) * 1029983 + (@PERSIAN_EPOCH - 1) 
    RETURN @jdn
End
Go
--Secondly, convert Julian calendar date to Gregorian to achieve the target.
Create FUNCTION [dbo].[UDF_Julian_To_Gregorian] (@jdn bigint)
Returns nvarchar(11)
as
Begin
    Declare @Jofst  as Numeric(18,2)
    Set @Jofst=2415020.5
    Return Convert(nvarchar(11),Convert(datetime,(@jdn- @Jofst),113),110)
End
Go
-- Here is the example
Select dbo.[UDF_Julian_To_Gregorian](dbo.[UDF_Persian_To_Julian](1391,1,30))
--Result is 04-18-2012

Another simple conversion is here:

 SELECT FORMAT(sysdatetimeoffset() at time zone 'Iran Standard Time' , 'yyyy/MM/dd-HH:mm:ss', 'fa')

Upvotes: 2

MohammadSoori
MohammadSoori

Reputation: 2408

I hope you found this useful.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:      MohammadSoori
-- Create date: 2021-06-21
-- Description: Convert Persian date to Miladi date.
-- =============================================
-- SELECT [dbo].[PersianToMiladi] ('1400/01/01')
-- =============================================
CREATE FUNCTION [dbo].[PersianToMiladi]
(
    @PersianDate VARCHAR(10)
)
RETURNS DATE
AS
BEGIN
    SET @PersianDate = RIGHT (@PersianDate, 9)
    DECLARE @Year INT = SUBSTRING(@PersianDate, 1, 3)
    DECLARE @Month INT = SUBSTRING(@PersianDate, 5, 2)
    DECLARE @Day INT = SUBSTRING(@PersianDate, 8, 2)
    DECLARE @DiffYear INT = @Year - 350

    DECLARE @Days INT = @DiffYear * 365.24 +
    CASE WHEN @Month < 7 THEN (@Month - 1) * 31
         ELSE 186 + (@Month - 7) * 30 END + @Day

    DECLARE @StartDate DATETIME = '03/21/1971'
    DECLARE @ResultDate DATE = @StartDate + @Days

    RETURN CONVERT(DATE, @ResultDate)  

END

Upvotes: 6

Ehsan Mirsaeedi
Ehsan Mirsaeedi

Reputation: 7592

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!

Upvotes: 8

Anon
Anon

Reputation: 10908

A few different approaches

1) Use SQL CLR to run C# code from within SQL Server

2) Find or write a correct implementation of Persian<->Gregorian conversion in T-SQL

3) Run your C# code for all the dates you care about and dump the output to a file. Import that file into a table. When you need to convert, just look up the answer.

Option (3) is probably going to be the easiest, most maintainable, and best-performing solution. The nice thing about dates is that there really aren't that many of them. A calendar table for a hundred years is just kilobytes of memory, and databases are pretty good at doing lookups.

Upvotes: 6

Related Questions