تبدیل تاریخ شمسی به میلادی–میلادی به شمسی– میلادی به قمری در Sql Server 2005-2008R2-2012

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))
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
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

set @mdays=dbo.NumberOfDayInMonthPersian(@Year,@m)
if(@m=@StartMonthGregorianDateInPersianCalendar)
set @mdays=@mdays-@StartDayGregorianDateInPersianCalendar+1

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

Create FUNCTION [dbo].[IsLeapYear]
(
@Year int
)
RETURNS bit
AS
BEGIN
DECLARE @ResultVar bit

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 @ResultVar

END

Create FUNCTION [dbo].[NumberOfDayInMonthPersian]
(
@Year int,
@Month int
)
RETURNS int
AS
BEGIN

DECLARE @ResultVar int
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 @ResultVar

END

Create FUNCTION [dbo].[NumberOfDaysInMonthGregorian]
(
@Year int
,@Month int
)
RETURNS int
AS
BEGIN

DECLARE @ResultVar int
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 @ResultVar

END

Create FUNCTION [dbo].[ShamsitoMiladi](@DateStr varchar(10))

RETURNS DATE

AS 

BEGIN

   declare @YYear int,@MMonth int,@DDay int,@epbase int,@epyear int,@mdays int,@persian_jdn int,@i int,

   @j int,@l int,@n int,@TMPRESULT varchar(10),@IsValideDate int,@TempStr varchar(20),@TmpDateStr varchar(10)

   SET @i=charindex(‘/’,@DateStr)

   IF LEN(@DateStr) – CHARINDEX(‘/’, @DateStr,CHARINDEX(‘/’, @DateStr,1)+1) = 4

   BEGIN

     IF ( ISDATE(@TmpDateStr) =1 ) 

       RETURN @TmpDateStr

     ELSE            

        RETURN NULL

   END

   ELSE

     SET @TmpDateStr = @DateStr

   IF ((@i<>0) and

        (ISNUMERIC(REPLACE(@TmpDateStr,‘/’,”))=1) and

        (charindex(‘.’,@TmpDateStr)=0))

   BEGIN

       SET @YYear=CAST(SUBSTRING(@TmpDateStr,1,@i–1) AS INT)

                IF ( @YYear< 1300 )

                      SET @YYear =@YYear + 1300

                IF @YYear > 9999

                  RETURN NULL

                                SET @TempStr= SUBSTRING(@TmpDateStr,@i+1,Len(@TmpDateStr))

                                SET @i=charindex(‘/’,@TempStr)

        SET @MMonth=CAST(SUBSTRING(@TempStr,1,@i–1) AS INT)

        SET @MMonth=@MMonth— -1

        SET @TempStr= SUBSTRING(@TempStr,@i+1,Len(@TempStr))  

        SET @DDay=CAST(@TempStr AS INT)

       SET @DDay=@DDay— – 1

        IF ( @YYear >= 0 )

                     SET @epbase = @YYear – 474

                 Else

                     SET @epbase = @YYear – 473

                 SET @epyear = 474 + (@epbase % 2820)

        IF (@MMonth <= 7 )

                       SET @mdays = ((@MMonth) – 1) * 31

        Else

            SET @mdays = ((@MMonth) – 1) * 30 + 6

            SET @persian_jdn =(@DDay)  + @mdays + CAST((((@epyear * 682) – 110) / 2816) as int)  + (@epyear – 1) * 365  +  CAST((@epbase / 2820)  as int ) * 1029983  + (1948321 – 1)

        IF (@persian_jdn > 2299160)

         BEGIN

            SET @l = @persian_jdn + 68569

            SET @n = CAST(((4 * @l) / 146097) as int)

            SET @l = @l –  CAST(((146097 * @n + 3) / 4) as int)

            SET @i =  CAST(((4000 * (@l + 1)) / 1461001) as int)

            SET @l = @l – CAST( ((1461 * @i) / 4) as int) + 31

            SET @j =  CAST(((80 * @l) / 2447) as int)

            SET @DDay = @l – CAST( ((2447 * @j) / 80) as int)

            SET @l =  CAST((@j / 11) as int)

            SET @MMonth = @j + 2 – 12 * @l

            SET @YYear = 100 * (@n – 49) + @i + @l

         END

                SET @TMPRESULT=Cast(@MMonth as varchar(2))+‘/’+CAST(@DDay  Varchar(2))+‘/’+CAST(@YYear as varchar(4)) 

                RETURN Cast(@TMPRESULT as varchar(10))

    END

    RETURN NULL     

END