میلادی به شمسی
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
شمسی به میلادی
CREATE FUNCTION [dbo].[ShamsiToMiladi](@DateStr varchar(10))
RETURNS DATETIME
AS
BEGIN
declare @YYear int
declare @MMonth int
declare @DDay int
declare @epbase int
declare @epyear int
declare @mdays int
declare @persian_jdn int
declare @i int
declare @j int
declare @l int
declare @n int
declare @TMPRESULT varchar(10)
declare @IsValideDate int
declare @TempStr varchar(20)
DECLARE @TmpDateStr varchar(10)
SET @i=charindex('/',@DateStr)
IF LEN(@DateStr) - CHARINDEX('/', @DateStr,CHARINDEX('/', @DateStr,1)+1) = 4
BEGIN
SET @TmpDateStr = dbo.ReversDate(@DateStr)
IF ( ISDATE(@TmpDateStr) =1 )
RETURN @TmpDateStr
ELSE
RETURN NULL
END
ELSE
SET @TmpDateStr = @DateStr
IF ((@i<>0) and
(dbo.SubStrCount('/', @TmpDateStr)=2) and
(ISNUMERIC(REPLACE(@TmpDateStr,'/',''))=1) and
(charindex('.',@TmpDateStr)=0)
)
BEGIN
SET @YYear=CAST(SUBSTRING(@TmpDateStr,1,@i-1) AS INT)
IF ( @YYear< 1300 )
SET @YYear =@YYear + 1300
IF @YYear > 9999
RETURN NULL
SET @TempStr= SUBSTRING(@TmpDateStr,@i+1,Len(@TmpDateStr))
SET @i=charindex('/',@TempStr)
SET @MMonth=CAST(SUBSTRING(@TempStr,1,@i-1) AS INT)
SET @MMonth=@MMonth-- -1
SET @TempStr= SUBSTRING(@TempStr,@i+1,Len(@TempStr))
SET @DDay=CAST(@TempStr AS INT)
SET @DDay=@DDay-- - 1
IF ( @YYear >= 0 )
SET @epbase = @YYear - 474
Else
SET @epbase = @YYear - 473
SET @epyear = 474 + (@epbase % 2820)
IF (@MMonth <= 7 )
SET @mdays = ((@MMonth) - 1) * 31
Else
SET @mdays = ((@MMonth) - 1) * 30 + 6
SET @persian_jdn =(@DDay) + @mdays + CAST((((@epyear * 682) - 110) / 2816) as int) + (@epyear - 1) * 365 + CAST((@epbase / 2820) as int ) * 1029983 + (1948321 - 1)
IF (@persian_jdn > 2299160)
BEGIN
SET @l = @persian_jdn + 68569
SET @n = CAST(((4 * @l) / 146097) as int)
SET @l = @l - CAST(((146097 * @n + 3) / 4) as int)
SET @i = CAST(((4000 * (@l + 1)) / 1461001) as int)
SET @l = @l - CAST( ((1461 * @i) / 4) as int) + 31
SET @j = CAST(((80 * @l) / 2447) as int)
SET @DDay = @l - CAST( ((2447 * @j) / 80) as int)
SET @l = CAST((@j / 11) as int)
SET @MMonth = @j + 2 - 12 * @l
SET @YYear = 100 * (@n - 49) + @i + @l
END
SET @TMPRESULT=Cast(@MMonth as varchar(2))+'/'+CAST(@DDay as Varchar(2))+'/'+CAST(@YYear as varchar(4))
RETURN Cast(@TMPRESULT as Datetime)
END
RETURN NULL
END
