X
تبلیغات
رایتل

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


  دوشنبه 18 دی‌ماه سال 1391
پیاده سازی تابع در ادامه مطلب می باشد.


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