تابع تبديل تاريخ ميلادي به شمسي در sql

[msh_gold]

با سلام من يه تابعي دارم كه كارش تبديل تاريخ تو بانكه ولي يه مشكل كوچك دار و اون اينه كه بعضي مواقع درست كار مي كنه وبعضي مواقع تاريخ رو پس و پيش نشون ميده كدش رو پايين گذاشتم از اساتيد خواهش مي كنم برسي كنيد ببينيد مشكلش كجاست

CREATE function GetpersionYearMonthDay (@date datetime )
returns varchar(15)
as
begin
declare @d1 bigint
set @d1=365
declare @d4 bigint
set @d4=4*@d1+1
declare @d33 bigint
set @d33=33*@d1+8
declare @ret varchar(10)
declare @ret1 varchar(4)
declare @ret2 varchar(2)
declare @ret3 varchar(2)
declare @y bigint
declare @m bigint
declare @d bigint
set @d=cast(@date as bigint )+422505
set @y=(@d/@d33)*33+122
set @d=@d%@d33

if (@d>(7*@d4+@d1))
begin
set @y=@y+1
set @d=@d-@d1
end
set @y=@y+(@d/@d4)*4
set @d=@d%@d4
declare @i bigint
set @i=@d/@d1
set @d=@d%@d1
if (@i=4)
begin
set @i=@i-1
set @d=@d+@d1
end
set @y=@y+@i

if (@d<186)
begin
set @m=(@d/31)+1
set @d=(@d%31)+1
end
else
begin
set @d=@d-186
set @m=(@d/30)+7
set @d=(@d%30)+1
end
set @ret1=convert(varchar,@y*10000)
set @ret2=convert(varchar,@m*100)
set @ret3=convert(varchar,@d)
set @ret2=reverse(@ret2)
set @ret=@ret1+'/'+@ret2+'/'+@ret3
return @ret
end

[karmand]

از این استفاده کن

ALTER FUNCTION dbo.hejri
(@dd datetime)
RETURNS char(10)
AS
BEGIN
DECLARE @mahs as char(2)
DECLARE @rozs as char(2)
DECLARE @diff As int
DECLARE @i As int
DECLARE @leap As int
DECLARE @roz AS int
DECLARE @mah As int
DECLARE @sal As int


SELECT @roz = 11
SELECT @mah = 10
SELECT @sal = 1358

SELECT @diff = DateDiff("d", cast('1980/01/01' as datetime), @dd) -- leap year

SELECT @i = 1

while @i <= @diff
BEGIN
SELECT @roz = @roz + 1


If @mah = 12 And ((@sal+1) - ((@sal+1)/4)*4) <> 0
If @roz > 29 BEGIN
SELECT @roz = 1
SELECT @mah = @mah + 1
End




If @mah > 12 BEGIN
SELECT @sal = @sal + 1
SELECT @mah = 1
End

If @mah > 6
If @roz > 30 BEGIN
SELECT @roz = 1
SELECT @mah = @mah + 1
End
if @mah <= 6
If @roz > 31 BEGIN
SELECT @roz = 1
SELECT @mah = @mah + 1
End
SELECT @i = @i + 1

END

if @mah < 10
SELECT @mahs = '0' + LTRIM(RTRIM(str(@mah)))
else
SELECT @mahs = LTRIM(RTRIM(str(@mah)))

if @roz < 10
SELECT @rozs = '0' + LTRIM(RTRIM(str(@roz)))
else
SELECT @rozs = LTRIM(RTRIM(str(@roz)))

RETURN LTRIM(RTRIM(str(@sal))) + '/' + LTRIM(RTRIM(@mahs)) + '/' + LTRIM(RTRIM(@rozs))
END