تابع تبدیل تاریخ میلادی به شمسی در sql server
create function [dbo].[getShamsiDate](@InputDate datetime)
returns nvarchar(10)
as
begin
Declare
@Year Integer,
@Month Integer,
@Day Integer,
@F_Year Integer,
@F_Month Integer,
@F_Day Integer,
@F_Day_Name Varchar(10),
@F_Month_Name Varchar(10),
@LastDay Integer,
@Plus Integer,
@Minus Integer,
@Intercalary Integer,
@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
