تاریخ شمسی در SQl

[hani_prog]

با سلام خدمت شما دوستان
در SQl 2000 تاریخ شمسی رو نمیشه وارد کرد و باید میلادی وارد کرد
می خواستم به دونم میشه تابعی در Query Analyzer نوشت که فیمد تاریخ میلادی رو
به شمسی در آوره
با آرزوی موفقیت برای شما

[qeidarlo]

--تابع تبدیل تاریخ میلادی به شمسی
CREATE FUNCTION dbo.MiladiToShamsi(@dt datetime)
RETURNS Nchar(10) AS
begin

Declare @smonths int

Declare @syy int
Declare @smm int
Declare @sdd int
Declare @Val int
Declare @By int
declare @mstart datetime
declare @Mc int

declare @sbase int
declare @sleapbmp nchar(150)
declare @ind as int
declare @Mdate nchar(10)
-- declare @dt datetime

set @mstart = '1900/03/21'
-- set @dt='2005/03/17'

set @Val =datediff(day,@mstart ,@dt )-- + 1
set @syy = '1279'
set @smm = 1
set @sdd = 1

set @sleapbmp='000010001000100010001000100010000100010 00100010001000100010001000010001000100010001000100 010001000010001000100010001000100010001'
set @sbase=475


While @Val > 365
Begin
set @ind = (@syy - @sbase) % (128 + 1)
if substring(@sleapbmp, @ind, 1) = '1'
Begin
set @Val = @Val - 1
End
set @Val = @Val - 365
set @syy = @syy + 1
end
set @smonths=31
----------------------------
While @Val > @smonths
Begin
set @Val = @Val - @smonths
set @smm = @smm + 1

if @smm=1 set @smonths= 31
if @smm=2 set @smonths = 31
if @smm=3 set @smonths = 31
if @smm=4 set @smonths = 31
if @smm=5 set @smonths = 31
if @smm=6 set @smonths = 31

if @smm=7 set @smonths = 30
if @smm=8 set @smonths = 30
if @smm=9 set @smonths = 30
if @smm=10 set @smonths = 30
if @smm=11 set @smonths = 30
if @smm=12 set @smonths = 29

end
set @sdd = @Val

If @smm = 12 And @sdd = 30
Begin
set @ind = (@By - @sbase) % (128 + 1)
if substring(@sleapbmp, @ind, 1) <> '1'
Begin
set @syy = @syy + 1
set @smm = 1
set @sdd = 1
end
end


Return LTrim(Rtrim(str(@syy))) + '/'
+ Case When Len(LTrim(Rtrim(str(@smm)))) < 2 Then '0' + LTrim(Rtrim(str(@smm)))
Else LTrim(Rtrim(str(@smm))) end + '/' + Case When Len(LTrim(Rtrim(str(@sdd)))) < 2 Then '0' + LTrim(Rtrim(str(@sdd))) Else LTrim(Rtrim(str(@sdd))) end

end

[qeidarlo]

--تابع تبدیل تاریخ شمسی به میلادی
CREATE FUNCTION dbo.ShamsiToMiladi(@FD NCHAR(10))
RETURNS smalldatetime AS
begin
Declare @syy bigint
Declare @smm bigint
Declare @sdd bigint
Declare @val bigint
Declare @By bigint
declare @mstart datetime
declare @Mc int

declare @sbase int
declare @sleapbmp nchar(150)
declare @ind as int



-- set @fd='1384/02/11'
set @mstart = '1900/03/21'

set @syy = substring(@FD,1,4)
set @smm = substring(@FD,6,2)
set @sdd = substring(@FD,9,2)
set @By = '1279'

set @sleapbmp='000010001000100010001000100010000100010 00100010001000100010001000010001000100010001000100 010001000010001000100010001000100010001'
set @sbase=475

-- print @smm

set @val = 0

While (@By < @syy)
begin

set @ind = (@By - @sbase) % (128 + 1)
if substring(@sleapbmp, @ind, 1) ='1'
Begin
set @val = @val + 1
end

set @val = @val + 365
set @By = @By + 1
end

set @Mc=1
while @mc< @smm

begin
Select @val =Case
When @Mc= 1 Then @Val+31
When @Mc= 2 Then @Val+31
When @Mc= 3 Then @Val+31
When @Mc= 4 Then @Val+31
When @Mc= 5 Then @Val+31
When @Mc= 6 Then @Val+31
When @Mc= 7 Then @Val+30
When @Mc= 8 Then @Val+30
When @Mc= 9 Then @Val+30
When @Mc= 10 Then @Val+30
When @Mc= 11 Then @Val+30
When @Mc= 12 Then @Val+29
end
set @mc=@mc+1
end
set @val = @val + @sdd
return DATEADD(day,@val,@mstart )
end

[qeidarlo]

--تابع تبدیل تاریخ شمسی به میلادی
CREATE FUNCTION dbo.ShamsiToMiladi(@FD NCHAR(10))
RETURNS smalldatetime AS
begin
Declare @syy bigint
Declare @smm bigint
Declare @sdd bigint
Declare @val bigint
Declare @By bigint
declare @mstart datetime
declare @Mc int

declare @sbase int
declare @sleapbmp nchar(150)
declare @ind as int



-- set @fd='1384/02/11'
set @mstart = '1900/03/21'

set @syy = substring(@FD,1,4)
set @smm = substring(@FD,6,2)
set @sdd = substring(@FD,9,2)
set @By = '1279'

set @sleapbmp='000010001000100010001000100010000100010 00100010001000100010001000010001000100010001000100 010001000010001000100010001000100010001'
set @sbase=475

-- print @smm

set @val = 0

While (@By < @syy)
begin

set @ind = (@By - @sbase) % (128 + 1)
if substring(@sleapbmp, @ind, 1) ='1'
Begin
set @val = @val + 1
end

set @val = @val + 365
set @By = @By + 1
end

set @Mc=1
while @mc< @smm

begin
Select @val =Case
When @Mc= 1 Then @Val+31
When @Mc= 2 Then @Val+31
When @Mc= 3 Then @Val+31
When @Mc= 4 Then @Val+31
When @Mc= 5 Then @Val+31
When @Mc= 6 Then @Val+31
When @Mc= 7 Then @Val+30
When @Mc= 8 Then @Val+30
When @Mc= 9 Then @Val+30
When @Mc= 10 Then @Val+30
When @Mc= 11 Then @Val+30
When @Mc= 12 Then @Val+29
end
set @mc=@mc+1
end
set @val = @val + @sdd
return DATEADD(day,@val,@mstart )
end

[kashaneh]

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



CREATE FUNCTION [dbo].[MiladiTOShamsi] (@MDate DateTime)
RETURNS Varchar(10)
AS
BEGIN
DECLARE @SYear as Integer
DECLARE @SMonth as Integer
DECLARE @SDay as Integer
DECLARE @AllDays as float
DECLARE @ShiftDays as float
DECLARE @OneYear as float
DECLARE @LeftDays as float
DECLARE @YearDay as Integer
DECLARE @Farsi_Date as Varchar(100)
SET @MDate=@MDate-CONVERT(char,@MDate,114)

SET @ShiftDays=466699 +2
SET @OneYear= 365.24199


SET @SYear = 0
SET @SMonth = 0
SET @SDay = 0
SET @AllDays = CAst(@Mdate as Real)

SET @AllDays = @AllDays + @ShiftDays

SET @SYear = (@AllDays / @OneYear) --trunc
SET @LeftDays = @AllDays - @SYear * @OneYear

if (@LeftDays < 0.5)
begin
SET @SYear=@SYear+1
SET @LeftDays = @AllDays - @SYear * @OneYear
end;

SET @YearDay = @LeftDays --trunc
if (@LeftDays - @YearDay) >= 0.5
SET @YearDay=@YearDay+1

if ((@YearDay / 31) > 6 )
begin
SET @SMonth = 6
SET @YearDay=@YearDay-(6 * 31)
SET @SMonth= @SMonth+( @YearDay / 30)
if (@YearDay % 30) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 7) * 30)
end
else
begin
SET @SMonth = @YearDay / 31
if (@YearDay % 31) <> 0
SET @SMonth=@SMonth+1
SET @YearDay=@YearDay-((@SMonth - 1) * 31)
end
SET @SDay = @YearDay
SET @SYear=@SYear+1

SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + CAST (@SMonth as VarChar(10)) + '/' + CAST (@SDay as VarChar(10))
Return @Farsi_Date

END