How to convert Persian (shamsi) date to Gregorian in T-SQL?

Question

I Use A lot data stored in Database to make a report and I was wondering if there is a fast way to convert all of the date in the tables to Persian date when selecting them.

there is some ways to convert but they aren't SQL based and so they are slower than a function just in the SQL.

so if any body knows how to do the converting in the SQL I will thank him a lot.

Answer 1

And a day of searching I decided to do my own style of solving.

and the result is here: Enjoy :D

USE [Test]
GO
/****** Object:  UserDefinedFunction [dbo].[MyG2J]    Script Date: 10/11/2019 02:35:43 ب.ظ ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[MyG2J](@inputDate DateTime)
                                        RETURNS nvarchar(max)
                                        begin
declare @mDay int,@mMonth int,@mYear int,@sDay int,@sMonth int,@sYear int,@mid int, @isMC bit=0,@isSC bit=0,@preSC bit = 0;

set @mDay=day(@inputDate);
set @mMonth=MONTH(@inputDate);
set @mYear=YEAR(@inputDate);

if @mYear%4=0
    set @isMC=1;
else
    set @isMC=0;

set @sYear=@mYear-622;

if (@sYear+1)%4=0
    set @preSC = 1;
else
    set @preSC=0;

select @mid=
case
    when @mMonth=1 then'20'
    when @mMonth=2 then'19'
    when @mMonth=3 then'20'
    when @mMonth=4 then'20'
    when @mMonth=5 then'21'
    when @mMonth=6 then'21'
    when @mMonth=7 then'22'
    when @mMonth=8 then'22'
    when @mMonth=9 then'22'
    when @mMonth=10 then'22'
    when @mMonth=11 then'21'
    when @mMonth=12 then'21'
end


if @isMC=1
    begin
        set @mid=@mid-1 
    end

if @mMonth=3 and @mDay=@mid
    set @sMonth=@mMonth+9;
else
begin
    if (@mMonth=3 and @mDay>@mid) or (@mMonth>3)
        begin
            set @sYear=@sYear+1;
            set @sMonth=@mMonth-3;
        end
    else
        set @sMonth=@mMonth+9;
end

if (@sYear+1)%4=0
    set @isSC = 1;
else
    set @isSC=0;

if @isMC=1
    BEGIN
        if @isSC=0
            if @mMonth!=3
                set @mid=@mid+1
    END
else
    begin
        if @isSC=1
            if @mMonth!=3
                set @mid=@mid-1
    end

if @mDay>@mid
    begin
        set @sDay=@mDay-@mid;
        set @sMonth=@sMonth+1;
    end
else
    begin
        if @sMonth<7
            set @sDay=31+(@mDay-@mid);
        else
            begin
                if @sMonth=12
                    BEGIN
                        if @isSC=1
                            set @sDay=30+(@mDay-@mid); 
                        else
                            set @sDay=29+(@mDay-@mid); 
                    END
                else
                    set @sDay=30+(@mDay-@mid);
            end
    end



return Cast(@sYear as nvarchar)+'/'+Cast(@sMonth as nvarchar)+'/'+Cast(@sDay as nvarchar)
end