تبلیغات
بانك های اطلاعاتی تحت داس و ویندوز - تابع تبدیل تاریخ میلادی به شمسی بصورت 23/02/1389 در sqlserver

تابع تبدیل تاریخ میلادی به شمسی بصورت 23/02/1389 در sqlserver

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


برچسب ها: تایع ، تبدیل ، میلادی ، شمسی ، sqlserver ،

 
لبخندناراحتچشمک
نیشخندبغلسوال
قلبخجالتزبان
ماچتعجبعصبانی
عینکشیطانگریه
خندهقهقههخداحافظ
سبزقهرهورا
دستگلتفکر
نظرات پس از تایید نشان داده خواهند شد.