تابع تبدیل تاریخ میلادی به شمسی در SQL Server

پیاده سازی تابع در ادامه مطلب می باشد.

CREATE FUNCTION [dbo].[GetShamsiDate](@InputDate datetime)

RETURNS nvarchar(10)

AS

BEGIN

DECLARE @Year Int,@Month Int,@Day Int,@F_Year Int,@F_Month Int,@F_Day Int,@F_Day_Name Varchar(10),

@F_Month_Name Varchar(10),@LastDay Int,@Plus Int,@Minus Int,@Intercalary Int,@S_Year Varchar(5),

@S_Month Varchar(5),@S_Day Varchar(5),@E_Date Varchar(20),@Ret Varchar(20),@FarsiFormattedDate Varchar(20)

SET @Plus = 0

SET @Year = Year(@InputDate)

SET @Month = Month(@InputDate)

SET @Day = Day(@InputDate)

SET @S_Year = Cast(@Year AS VarChar(5))

SET @S_Month = Cast(@Month AS VarChar(5))

SET @S_Day = Cast(@Day AS VarChar(5))

IF Len(@S_Month) < 2

SET @S_Month = '0' + @S_Month

IF Len(@S_Day) < 2

SET @S_Day = '0'+@S_Day

SET @E_Date = @S_Year + @S_Month + @S_Day

SET @F_Day_Name = CASE DATEPART(dw, @InputDate)

WHEN 1 THEN ''

WHEN 2 THEN ''

WHEN 3 THEN ''

WHEN 4 THEN ''

WHEN 5 THEN ''

WHEN 6 THEN ''

WHEN 7 THEN ''

END

IF ((@Month = 1) or (@Month = 5) or (@Month = 6))

SET @Plus = 10

IF ((@Month = 2) or (@Month = 4))

SET @Plus = 11

IF ((@Month = 3) or (@Month = 7) or (@Month = 8) or (@Month = 9) or (@Month = 11) or (@Month = 12))

SET @Plus = 9

IF (@Month = 10)

SET @Plus = 8

SET @Year = @Year % 100

SET @Intercalary = @Year

IF (@Intercalary % 4 = 0)

IF (@Month > 2)

SET @Plus = @Plus + 1

IF ((@Intercalary - 1) % 4 = 0)

BEGIN

SET @LastDay = 30

IF (@Month <= 3)

SET @Plus = @Plus + 1

END

ELSE

SET @LastDay = 29

SET @F_Year = @Year - 22

IF (@F_Year < 0)

SET @F_Year = @F_Year + 100

SET @F_Month = @Month + 9

IF (@F_Month > 12)

BEGIN

SET @F_Month = @F_Month - 12

SET @F_Year = @F_Year + 1

END

SET @F_Day = @Day + @Plus

IF (@F_Month <= 6)

SET @Minus = 31

ELSE

IF ((@F_Month > 6) and (@F_Month<12))

SET @Minus = 30

ELSE

SET @Minus = @LastDay

IF (@F_Day > @Minus)

BEGIN

SET @F_Day = @F_Day - @Minus;

SET @F_Month = @F_Month + 1

END

IF (@F_Month > 12)

BEGIN

SET @F_Month = @F_Month - 12;

SET @F_Year = @F_Year + 1

END;

IF @F_Year >= 10

SET @Ret = Cast(@F_Year As Varchar(4))

ELSE

SET @Ret = '0'+ Cast(@F_Year As Varchar(4))

SET @FarsiFormattedDate ='13'+@Ret

IF @F_Month >= 10

BEGIN

SET @Ret = @Ret + Cast(@F_Month As Varchar(4))

SET @FarsiFormattedDate =@FarsiFormattedDate+'/'+Cast(@F_Month As Varchar(4))

END

ELSE

BEGIN

SET @FarsiFormattedDate =@FarsiFormattedDate+'/0'+ Cast(@F_Month As Varchar(4))

SET @Ret = @Ret +'0'+ Cast(@F_Month As Varchar(4))

END

SET @F_Month_Name = CASE @F_Month

WHEN 1 THEN ''

WHEN 2 THEN ''

WHEN 3 THEN ''

WHEN 4 THEN ''

WHEN 5 THEN ''

WHEN 6 THEN ''

WHEN 7 THEN ''

WHEN 8 THEN ''

WHEN 9 THEN ''

WHEN 10 THEN ''

WHEN 11 THEN ''

WHEN 12 THEN ''

END

IF @F_Day >= 10

BEGIN

SET @FarsiFormattedDate=@FarsiFormattedDate+'/'+Cast(@F_Day As Varchar(4))

SET @Ret = @Ret + Cast(@F_Day As Varchar(4))

END

ELSE

BEGIN

SET @Ret = @Ret + '0'+ Cast(@F_Day As Varchar(4))

SET @FarsiFormattedDate=@FarsiFormattedDate+'/0'+ Cast(@F_Day As Varchar(4))

END

RETURN ISNULL(@FarsiFormattedDate,'')

END