T-SQL Gregorian To Persian Converter

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube

There are lots of times which you need a "Gregorian to Persian ( Shamsi ) Converter" in t-sql.

below code will convert gregorian date string for example 2011-08-25 to persian or shamsi date string for example 1390-06-03 .

just note this code will work on sql server 2005 or above because I used the Floor function.

 Main function is the [dbo].[GregorianToPersian]  which you can use it in this syntax:

select [dbo].[GregorianToPersian] (‘2011-08-25)

result: 1390-06-03

here all code exists:

USE [master]
GO
/****** Object:  UserDefinedFunction [dbo].[IsLeapYear]    Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Reza Rad
— Create date: 8/25/2011
— Description:    IsLeapYear
— =============================================
CREATE FUNCTION [dbo].[IsLeapYear]
(
    @Year int
)
RETURNS bit
AS
BEGIN
    — Declare the return variable here
    DECLARE @ResultVar bit

    — Add the T-SQL statements to compute the return value here
    if @Year % 400 = 0
       Begin
            set @ResultVar=1
       end
    else if @Year % 100 = 0
       Begin
            set @ResultVar=0
       end
    else if @Year % 4 = 0
       Begin
            set @ResultVar=1
       end
    else
       Begin
            set @ResultVar=0
       end

    — Return the result of the function
    RETURN @ResultVar

END
GO
/****** Object:  UserDefinedFunction [dbo].[NumberOfDaysInMonthGregorian]    Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Reza Rad
— Create date: 8/25/2011
— Description:    Fetch number of days in gregorian month
— =============================================
CREATE FUNCTION [dbo].[NumberOfDaysInMonthGregorian]
(
    @Year int
    ,@Month int
)
RETURNS int
AS
BEGIN
    — Declare the return variable here
    DECLARE @ResultVar int

    — Add the T-SQL statements to compute the return value here
    if(@Month<>2)
    begin
        set @ResultVar=30+((@Month + FLOOR(@Month/8)) % 2)
    end
    else
    begin
        if(dbo.IsLeapYear(@Year)=1)
        begin
            set @ResultVar=29
        end
        else
        begin
            set @ResultVar=28
        end
    end

    — Return the result of the function
    RETURN @ResultVar

END
GO
/****** Object:  UserDefinedFunction [dbo].[NumberOfDayInMonthPersian]    Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Reza Rad
— Create date: 8/25/2011
— Description:    Fetch number of days in Persian month
— =============================================
CREATE FUNCTION [dbo].[NumberOfDayInMonthPersian]
(
    @Year int,– Gregorian Year
    @Month int — Presian Month
)
RETURNS int
AS
BEGIN
    — Declare the return variable here
    DECLARE @ResultVar int

    — Add the T-SQL statements to compute the return value here
    if(@Month<=6)
        set @ResultVar=31
    else
        if(@Month=12)
            if(dbo.IsLeapYear(@Year-1)=1)
                set @ResultVar=30
            else
                set @ResultVar=29
        else
            set @ResultVar=30

    — Return the result of the function
    RETURN @ResultVar

END
GO
/****** Object:  UserDefinedFunction [dbo].[GregorianToPersian]    Script Date: 08/25/2011 12:16:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Reza Rad
— Create date: 8/25/2011
— Description:    Convert gregorian date in type of yyyy-MM-dd format to persian/shamsi date string
— =============================================
CREATE FUNCTION [dbo].[GregorianToPersian]
(
    @Date varchar(10)
)
RETURNS varchar(10)
AS
BEGIN
    — Declare the return variable here
    DECLARE @ResultVar varchar(10)

    — Add the T-SQL statements to compute the return value here
    declare @Year int
    declare @Month int
    declare @Day int
    declare @PersianYear int
    declare @PersianMonth int
    declare @PersianDay int
    declare @StartMonthGregorianDateInPersianCalendar int=10
    declare @StartDayGregorianDateInPersianCalendar int=11

    set @Year=convert(int,substring(@Date,1,4))
    set @Month=convert(int,substring(@Date,6,2))
    set @Day=convert(int,substring(@Date,9,2))

    –print @year
    –print @month
    –print @day
    declare @GregorianDayIndex int=0

    if(dbo.IsLeapYear(@Year)=1)
        set @StartDayGregorianDateInPersianCalendar=11
    else
        if(dbo.IsLeapYear(@Year-1)=1)
            set @StartDayGregorianDateInPersianCalendar=12
        else
            set @StartDayGregorianDateInPersianCalendar=11

    –print @StartDayGregorianDateInPersianCalendar           

    declare @m_index int=1
    while @m_index<=@Month-1
    begin
        set @GregorianDayIndex=@GregorianDayIndex+dbo.NumberOfDaysInMonthGregorian(@Year,@m_index)
        set @m_index=@m_index+1
    end
    set @GregorianDayIndex=@GregorianDayIndex+@Day

    if(@GregorianDayIndex>=80)
    begin
        set @PersianYear=@Year-621
    end
    else
    begin
        set @PersianYear=@Year-622
    end

    declare @mdays int
    declare @m int
    declare @index int=@GregorianDayIndex
    set @m_index=0
    while 1=1
    begin
        if(@m_index<=2)
            set @m=@StartMonthGregorianDateInPersianCalendar+@m_index
        else
            set @m=@m_index-2

        –print ‘m=’+convert(varchar(max),@m    )
        set @mdays=dbo.NumberOfDayInMonthPersian(@Year,@m)
        if(@m=@StartMonthGregorianDateInPersianCalendar)
            set @mdays=@mdays-@StartDayGregorianDateInPersianCalendar+1

        –print ‘mday=’+convert(varchar(max),@mdays)
        –print ‘index=’+convert(varchar(max),@index)
        –print ‘m_index=’+convert(varchar(max),@m_index)

        if(@index<=@mdays)
        begin
            set @PersianMonth=@m
            if(@m=@StartMonthGregorianDateInPersianCalendar)
                set @PersianDay=@index+@StartDayGregorianDateInPersianCalendar-1
            else
                set @PersianDay=@index
            break
        end
        else
        begin
            set @index=@index-@mdays
            set @m_index=@m_index+1
        end           
    end

    set @ResultVar=
    convert(varchar(4),@PersianYear)+’-‘+
    right(‘0’+convert(varchar(2),@PersianMonth),2)+’-‘+
    right(‘0’+convert(varchar(2),@PersianDay),2)

    — Return the result of the function
    RETURN @ResultVar

END
GO

Facebooktwittergoogle_plusredditpinterestlinkedintumblrmailFacebooktwittergoogle_plusredditpinterestlinkedintumblrmail
FacebooktwitterlinkedinrssyoutubeFacebooktwitterlinkedinrssyoutube
Reza Rad on FacebookReza Rad on LinkedinReza Rad on TwitterReza Rad on Youtube
Reza Rad
Trainer, Consultant, Mentor
Reza Rad is a Microsoft Regional Director, an Author, Trainer, Speaker and Consultant. He has a BSc in Computer engineering; he has more than 20 years’ experience in data analysis, BI, databases, programming, and development mostly on Microsoft technologies. He is a Microsoft Data Platform MVP for nine continuous years (from 2011 till now) for his dedication in Microsoft BI. Reza is an active blogger and co-founder of RADACAD. Reza is also co-founder and co-organizer of Difinity conference in New Zealand.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
He wrote some books on MS SQL BI and also is writing some others, He was also an active member on online technical forums such as MSDN and Experts-Exchange, and was a moderator of MSDN SQL Server forums, and is an MCP, MCSE, and MCITP of BI. He is the leader of the New Zealand Business Intelligence users group. He is also the author of very popular book Power BI from Rookie to Rock Star, which is free with more than 1700 pages of content and the Power BI Pro Architecture published by Apress.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.

Leave a Reply

Your email address will not be published. Required fields are marked *