تبدیل تاریخ با SP

[mohsen_f_b]

سلام
دوستان کسی دستور تبدیل تاریخ میلادی به شمسی رو با SP داره؟
ممنونم

[بهنام بهمنی]

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go


-- =============================================
-- Author: Behnam Bahmani
-- Create date: 2005/02/27
-- Description: <Description, ,>
-- =============================================
ALTER FUNCTION [dbo].[GregorianToShamsi] (@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
declare @mymonth as Nvarchar(5)
if (@SMonth<10)
begin
SET @mymonth='0'+CAST (@SMonth as VarChar(10))
end
else
SET @mymonth=CAST (@SMonth as VarChar(10))
declare @myday as Nvarchar(5)
if (@SDay<10)
begin
SET @myday='0'+CAST (@SDay as VarChar(10))
end
else
SET @myday=CAST (@SDay as VarChar(10))

SET @Farsi_Date = CAST (@SYear as VarChar(10)) + '/' + @mymonth + '/' + @myday
Return @Farsi_Date


END

[majjjj]

این کد sql که خواستین


select dbo.hejri(M_DATE) from table1

و این هم فانکشن hejri برای تبدیل تاریخ به شمسی

CREATE 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