تبدیل تاریخ میلادی به شمسی و بالعکس

سلام
نوشتن این کد چند ساعت وقت گرفت، برای استفاده دوستان upload می کنم.
موفق باشید.

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

-- T-SQL Functions, Procedures for converting DATE from gregorian to solar and revers       

--

--  Author: 

--   owner of sasan_vm@yahoo.com :), please send any bug to my email

--   

--  OFFER TO:

--	barnamenevis.org

--

--  USAGE:

-- 	in all functions this strategy used

-- 	@type is smallint

-- 	@type=0  meaning solar date

-- 	@type=1  meaning gregorian date

--

--  	ToSolar @date      : input datetime, output solar date as varchar(10)    

--  	ToGregorian @date  : input solar date varchar(10), output datetime

--	SolarYear @date    : input datetime , output solar year as smallint

--	SolarMonth @date   : input datetime , output solar month as smallint

--	SolarDay @date     : input datetime , output solar day as smallint

--

--  EXAMPLE:

--

--	declare @date datetime

--	set @date = getdate()

--

--	exec dbo.ToGregorian '1389/5/1'

--	exec dbo.ToSolar @date

--	exec dbo.SolarYear @date

--	exec dbo.SolarMonth @date

--	exec dbo.SolarDay @date 

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------



CREATE FUNCTION [dbo].[LeapMonth] (@type SMALLINT)  

RETURNS SMALLINT AS

BEGIN 



DECLARE @array VARCHAR(5)

DECLARE @result SMALLINT



SET @array = '12,02' -- esfand, february

SET @result = CAST(SUBSTRING(@array, @type*2+@type+1, 2) AS SMALLINT)



RETURN @result



END

GO





CREATE FUNCTION [dbo].[DaysOfMonths] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 



DECLARE @array_s VARCHAR(38) -- shamsi month days

DECLARE @array_g VARCHAR(38) -- gregorian month days

DECLARE @result SMALLINT



SET @array_s = '00,31,31,31,31,31,31,30,30,30,30,30,29'

SET @array_g = '00,31,28,31,30,31,30,31,31,30,31,30,31'



IF ( @type = 0 )

	SET @result = CAST(SUBSTRING(@array_s, @month*2+@month+1, 2) AS SMALLINT)

ELSE

	SET @result = CAST(SUBSTRING(@array_g, @month*2+@month+1, 2) AS SMALLINT)





RETURN @result



END

GO





CREATE FUNCTION [dbo].[DaysToMonth] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 



DECLARE @array_s VARCHAR(55) -- shamsi month days

DECLARE @array_g VARCHAR(55) -- gregorian month days

DECLARE @result SMALLINT



SET @array_s = '000,000,031,062,093,124,155,186,216,246,276,306,3  36,365'

SET @array_g = '000,000,031,059,090,120,151,181,212,243,273,304,3  34,365'



IF ( @type = 0 )

	SET @result = CAST(SUBSTRING(@array_s, @month*3+@month+1, 3) AS SMALLINT)

ELSE

	SET @result = CAST(SUBSTRING(@array_g, @month*3+@month+1, 3) AS SMALLINT)





RETURN @result



END

GO





CREATE FUNCTION [dbo].[IsLeapYear] (@type SMALLINT, @year SMALLINT)  

RETURNS BIT AS

BEGIN 



DECLARE @result BIT



IF ( @type = 0 )

BEGIN

	IF ( ((((@year + 38) * 31) % 128) <= 30) )

		SET @result = 1

	ELSE

		SET @result = 0

END

ELSE

BEGIN

	IF ( ((@year%4) = 0) and (((@year%100) != 0) or ((@year%400) = 0)) )

		SET @result = 1

	ELSE

		SET @result = 0

END



RETURN @result



END

GO





CREATE FUNCTION [dbo].[IsDateValid] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS BIT AS

BEGIN 



DECLARE @result BIT



IF ( (@year != 0) AND (@month >= 1) AND (@month <= 12) AND

	        (@day >= 1) AND (@day <= dbo.DaysOfMonth(@type, @year, @month)) )

	SET @result = 1

ELSE

	SET @result = 0



RETURN @result



END

GO





CREATE FUNCTION [dbo].[DaysOfMonth] (@type SMALLINT, @year SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 



DECLARE @result SMALLINT



IF ( (@year != 0) AND (@month >= 1 AND @month <= 12) )

BEGIN

	SET @result = dbo.DaysOfMonths(@type, @month)

	IF ( (@month = dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year)=1) )

		SET @result = @result+1

END	

ELSE

	SET @result = 0





RETURN @result



END

GO





CREATE FUNCTION [dbo].[DaysToDate] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS SMALLINT AS

BEGIN 



DECLARE @result SMALLINT



IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1)

BEGIN

	SET @result = dbo.DaysToMonth(@type, @month) + @day

	IF ( (@month > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

		SET @result = @result+1

END

ELSE

	SET @result = 0



RETURN @result



END

GO





CREATE PROC [dbo].[DateOfDay] @type SMALLINT, @days SMALLINT, @year SMALLINT, @month SMALLINT OUTPUT, @day SMALLINT OUTPUT, @result BIT OUTPUT

AS



DECLARE @leapday SMALLINT

DECLARE @m SMALLINT



SET @leapday = 0

SET @month = 0

SET @day = 0

SET @m = 2



WHILE (@m <= 13)

BEGIN

	IF ( (@m > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

		SET @leapday = 1

	IF ( @days <= (dbo.DaysToMonth(@type, @m) + @leapday) )

	BEGIN

		SET @month = @m - 1

		IF ( @month <= dbo.LeapMonth(@type) ) 

			SET @leapday = 0

		SET @day = @days - (dbo.DaysToMonth(@type, @month) + @leapday)

		BREAK

	END



	SET @m = @m+1

END

SET @result = dbo.IsDateValid(@type, @year, @month, @day)



GO





CREATE PROC [dbo].[GregorianToSolar] @year SMALLINT OUTPUT, @month SMALLINT OUTPUT, @day SMALLINT OUTPUT, @result BIT OUTPUT

AS



DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevgregorianleap BIT



IF ( dbo.IsDateValid(1, @year, @month, @day) = 1 )

BEGIN

	SET @prevgregorianleap = dbo.IsLeapYear(1, @year-1)

	SET @days = dbo.DaysToDate(1, @year, @month, @day)

	SET @year = @year - 622

	IF ( dbo.IsLeapYear(0, @year) = 1 )

		SET @leapday = 1

	ELSE

		SET @leapday = 0

	IF ( (@prevgregorianleap = 1) AND (@leapday = 1) )

		SET @days = @days + 287

	ELSE

		SET @days = @days + 286

	IF ( @days > (365 + @leapday) )

	BEGIN

		SET @year = @year+1

		SET @days = @days - (365 + @leapday)

	END

	EXEC dbo.DateOfDay 0, @days, @year, @month OUT, @day OUT, @result OUT

END

ELSE

	SET @result = 0



GO





CREATE PROC [dbo].[SolarToGregorian] @year SMALLINT OUTPUT, @month SMALLINT OUTPUT, @day SMALLINT OUTPUT, @result BIT OUTPUT

AS



DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevsolarleap BIT



IF ( dbo.IsDateValid(0, @year, @month, @day) = 1 )

BEGIN

	SET @prevsolarleap= dbo.IsLeapYear(0, @year-1)

	SET @days = dbo.DaysToDate(0, @year, @month, @day)

	SET @year = @year + 621

	IF ( dbo.IsLeapYear(1, @year) = 1 )

		SET @leapday = 1

	ELSE

		SET @leapday = 0

	IF ( (@prevsolarleap = 1) AND (@leapday = 1) )

		SET @days = @days + 80

	ELSE

		SET @days = @days + 79

	IF ( @days > (365 + @leapday) )

	BEGIN

		SET @year = @year+1

		SET @days = @days - (365 + @leapday)

	END

	EXEC dbo.DateOfDay 1, @days, @year, @month OUT, @day OUT, @result OUT

END

ELSE

	SET @result = 0



GO





CREATE PROC [dbo].[ToSolar] @date DATETIME

AS



DECLARE @result VARCHAR(10)

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT



SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)



EXEC dbo.GregorianToSolar @year OUT, @month OUT, @day OUT, @flag OUT

IF ( @flag = 1 )

	SET @result = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR)

ELSE

	SET @result = 'ERROR'



SELECT @result



GO





CREATE PROC [dbo].[ToGregorian] @date VARCHAR (10)

AS



DECLARE @result DATETIME

DECLARE @temp VARCHAR(10)

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

DECLARE @flag BIT



SET @pos1 = CHARINDEX('/', @date)

SET @pos2 = CHARINDEX('/', @date, @pos1+1)

SET @year = CAST(LEFT(@date, @pos1-1) AS SMALLINT)

SET @month = CAST(SUBSTRING(@date, @pos1+1, @pos2-@pos1-1) AS SMALLINT) 

SET @day = CAST(SUBSTRING(@date, @pos2+1, LEN(@date)-@pos2) AS SMALLINT)



EXEC dbo.SolarToGregorian @year OUT, @month OUT, @day OUT, @flag OUT

IF ( @flag = 1 )

	SET @temp = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR)

ELSE

	SET @temp = 'ERROR'



SET @result = CAST(@temp AS DATETIME)

SELECT @result



GO





CREATE PROC [dbo].[SolarYear] @date DATETIME

AS



DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT



SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)



EXEC dbo.GregorianToSolar @year OUT, @month OUT, @day OUT, @flag OUT

IF ( @flag = 1 )

	SET @result = @year

ELSE

	SET @result = 0



SELECT @result



GO





CREATE PROC [dbo].[SolarMonth] @date DATETIME

AS



DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT



SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)



EXEC dbo.GregorianToSolar @year OUT, @month OUT, @day OUT, @flag OUT

IF ( @flag = 1 )

	SET @result = @month

ELSE

	SET @result = 0



SELECT @result



GO





CREATE PROC [dbo].[SolarDay] @date DATETIME

AS



DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT



SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)



EXEC dbo.GregorianToSolar @year OUT, @month OUT, @day OUT, @flag OUT

IF ( @flag = 1 )

	SET @result = @day

ELSE

	SET @result = 0



SELECT @result



GO

در این نسخه procedure ها کلا حذف و به function تبدیل شده اند که بتوان در query ها و توابع از آن استفاده کرد:

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

-- T-SQL Functions for converting DATE from gregorian to solar and revers       

--

--  Author: 

--   owner of sasan_vm@yahoo.com :), please send any bug to my email

--   

--  OFFER TO:

-- barnamenevis.org

--

--  USAGE:

--  in all functions this strategy used

--  @type is smallint

--  @type=0  meaning solar date

--  @type=1  meaning gregorian date

--

--   ToSolar ( @date )     : input datetime, output solar date as varchar(10)    

--   ToGregorian ( @date ) : input solar date varchar(10), output datetime

-- SolarYear ( @date )   : input datetime , output solar year as smallint

-- SolarMonth ( @date )  : input datetime , output solar month as smallint

-- SolarDay ( @date )    : input datetime , output solar day as smallint

--

--  EXAMPLE:

--

-- select dbo.ToGregorian( '1389/5/1' )

-- select dbo.ToSolar( getdate() )

-- select dbo.SolarYear( getdate() )

-- select dbo.SolarMonth( getdate() )

-- select dbo.SolarDay( getdate() )

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[LeapMonth] (@type SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array VARCHAR(5)

DECLARE @result SMALLINT

SET @array = '12,02' -- esfand, february

SET @result = CAST(SUBSTRING(@array, @type*2+@type+1, 2) AS SMALLINT)

RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysOfMonths] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array_s VARCHAR(38) -- shamsi month days

DECLARE @array_g VARCHAR(38) -- gregorian month days

DECLARE @result SMALLINT

SET @array_s = '00,31,31,31,31,31,31,30,30,30,30,30,29'

SET @array_g = '00,31,28,31,30,31,30,31,31,30,31,30,31'

IF ( @type = 0 )

 SET @result = CAST(SUBSTRING(@array_s, @month*2+@month+1, 2) AS SMALLINT)

ELSE

 SET @result = CAST(SUBSTRING(@array_g, @month*2+@month+1, 2) AS SMALLINT)



RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysToMonth] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array_s VARCHAR(55) -- shamsi month days

DECLARE @array_g VARCHAR(55) -- gregorian month days

DECLARE @result SMALLINT

SET @array_s = '000,000,031,062,093,124,155,186,216,246,276,306,3  36,365'

SET @array_g = '000,000,031,059,090,120,151,181,212,243,273,304,3  34,365'

IF ( @type = 0 )

 SET @result = CAST(SUBSTRING(@array_s, @month*3+@month+1, 3) AS SMALLINT)

ELSE

 SET @result = CAST(SUBSTRING(@array_g, @month*3+@month+1, 3) AS SMALLINT)



RETURN @result

END

GO



CREATE FUNCTION [dbo].[IsLeapYear] (@type SMALLINT, @year SMALLINT)  

RETURNS BIT AS

BEGIN 

DECLARE @result BIT

IF ( @type = 0 )

BEGIN

 IF ( ((((@year + 38) * 31) % 128) <= 30) )

  SET @result = 1

 ELSE

  SET @result = 0

END

ELSE

BEGIN

 IF ( ((@year%4) = 0) and (((@year%100) != 0) or ((@year%400) = 0)) )

  SET @result = 1

 ELSE

  SET @result = 0

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[IsDateValid] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS BIT AS

BEGIN 

DECLARE @result BIT

IF ( (@year != 0) AND (@month >= 1) AND (@month <= 12) AND

         (@day >= 1) AND (@day <= dbo.DaysOfMonth(@type, @year, @month)) )

 SET @result = 1

ELSE

 SET @result = 0

RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysOfMonth] (@type SMALLINT, @year SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @result SMALLINT

IF ( (@year != 0) AND (@month >= 1 AND @month <= 12) )

BEGIN

 SET @result = dbo.DaysOfMonths(@type, @month)

 IF ( (@month = dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year)=1) )

  SET @result = @result+1

END 

ELSE

 SET @result = 0



RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysToDate] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @result SMALLINT

IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1)

BEGIN

 SET @result = dbo.DaysToMonth(@type, @month) + @day

 IF ( (@month > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

  SET @result = @result+1

END

ELSE

 SET @result = 0

RETURN @result

END

GO



CREATE FUNCTION [dbo].[GregorianToSolar] (@year SMALLINT, @month SMALLINT, @day SMALLINT)

RETURNS VARCHAR(10) AS

BEGIN

DECLARE @result VARCHAR(10)

DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevgregorianleap BIT

SET @result = 'ERROR'

IF ( dbo.IsDateValid(1, @year, @month, @day) = 1 )

BEGIN

 SET @prevgregorianleap = dbo.IsLeapYear(1, @year-1)

 SET @days = dbo.DaysToDate(1, @year, @month, @day)

 SET @year = @year - 622

 IF ( dbo.IsLeapYear(0, @year) = 1 )

  SET @leapday = 1

 ELSE

  SET @leapday = 0

 IF ( (@prevgregorianleap = 1) AND (@leapday = 1) )

  SET @days = @days + 287

 ELSE

  SET @days = @days + 286

 IF ( @days > (365 + @leapday) )

 BEGIN

  SET @year = @year+1

  SET @days = @days - (365 + @leapday)

 END

 --

 -- EXEC dbo.DateOfDay 0, @days, @year, @month OUT, @day OUT, @result OUT

 -- insert procedure body for converting procedure to function, for using in query

 DECLARE @type SMALLINT

 DECLARE @m SMALLINT

 SET @type = 0

 SET @leapday = 0

 SET @month = 0

 SET @day = 0

 SET @m = 2

 WHILE (@m <= 13)

 BEGIN

  IF ( (@m > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

   SET @leapday = 1

  IF ( @days <= (dbo.DaysToMonth(@type, @m) + @leapday) )

  BEGIN

   SET @month = @m - 1

   IF ( @month <= dbo.LeapMonth(@type) ) 

    SET @leapday = 0

   SET @day = @days - (dbo.DaysToMonth(@type, @month) + @leapday)

   BREAK

  END

  SET @m = @m+1

 END

 IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1 )

  SET @result = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR) 

 -- End procedure

 --

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarToGregorian] (@year SMALLINT, @month SMALLINT, @day SMALLINT)

RETURNS DATETIME AS

BEGIN

DECLARE @result DATETIME

DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevsolarleap BIT

SET @result = 0 -- Jan  1 1900 12:00AM

IF ( dbo.IsDateValid(0, @year, @month, @day) = 1 )

BEGIN

 SET @prevsolarleap= dbo.IsLeapYear(0, @year-1)

 SET @days = dbo.DaysToDate(0, @year, @month, @day)

 SET @year = @year + 621

 IF ( dbo.IsLeapYear(1, @year) = 1 )

  SET @leapday = 1

 ELSE

  SET @leapday = 0

 IF ( (@prevsolarleap = 1) AND (@leapday = 1) )

  SET @days = @days + 80

 ELSE

  SET @days = @days + 79

 IF ( @days > (365 + @leapday) )

 BEGIN

  SET @year = @year+1

  SET @days = @days - (365 + @leapday)

 END

 -- EXEC dbo.DateOfDay 1, @days, @year, @month OUT, @day OUT, @result OUT

 -- insert procedure body for converting procedure to function, for using in query

 DECLARE @type SMALLINT

 DECLARE @temp VARCHAR(10)

 DECLARE @m SMALLINT

 SET @type = 1 

 SET @leapday = 0

 SET @month = 0

 SET @day = 0

 SET @m = 2

 WHILE (@m <= 13)

 BEGIN

  IF ( (@m > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

   SET @leapday = 1

  IF ( @days <= (dbo.DaysToMonth(@type, @m) + @leapday) )

  BEGIN

   SET @month = @m - 1

   IF ( @month <= dbo.LeapMonth(@type) ) 

    SET @leapday = 0

   SET @day = @days - (dbo.DaysToMonth(@type, @month) + @leapday)

   BREAK

  END

  SET @m = @m+1

 END

 IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1 )

 BEGIN

  SET @temp = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR)

  SET @result = CAST(@temp AS DATETIME) 

 END

 -- End procedure

 --

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[ToSolar] (@date DATETIME)

RETURNS VARCHAR(10) AS

BEGIN

DECLARE @result VARCHAR(10)

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

RETURN dbo.GregorianToSolar (@year, @month, @day)

END

GO



CREATE FUNCTION [dbo].[ToGregorian] (@date VARCHAR (10))

RETURNS DATETIME AS

BEGIN

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @pos1 = CHARINDEX('/', @date)

SET @pos2 = CHARINDEX('/', @date, @pos1+1)

SET @year = CAST(LEFT(@date, @pos1-1) AS SMALLINT)

SET @month = CAST(SUBSTRING(@date, @pos1+1, @pos2-@pos1-1) AS SMALLINT) 

SET @day = CAST(SUBSTRING(@date, @pos2+1, LEN(@date)-@pos2) AS SMALLINT)

RETURN dbo.SolarToGregorian (@year, @month, @day)

END

GO



CREATE FUNCTION [dbo].[SolarYear] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @result = CAST(LEFT(@temp, @pos1-1) AS SMALLINT)

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarMonth] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @pos2 = CHARINDEX('/', @temp, @pos1+1)

 SET @result = CAST(SUBSTRING(@temp, @pos1+1, @pos2-@pos1-1) AS SMALLINT) 

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarDay] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @pos2 = CHARINDEX('/', @temp, @pos1+1)

 SET @result = CAST(SUBSTRING(@temp, @pos2+1, LEN(@temp)-@pos2) AS SMALLINT)

END

RETURN @result

END

GO

نقل قول:

---

نوشته شده توسط sasan_vm

در این نسخه procedure ها کلا حذف و به function تبدیل شده اند که بتوان در query ها و توابع از آن استفاده کرد:

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

-- T-SQL Functions for converting DATE from gregorian to solar and revers       

--

--  Author: 

--   owner of sasan_vm@yahoo.com :), please send any bug to my email

--   

--  OFFER TO:

-- barnamenevis.org

--

--  USAGE:

--  in all functions this strategy used

--  @type is smallint

--  @type=0  meaning solar date

--  @type=1  meaning gregorian date

--

--   ToSolar ( @date )     : input datetime, output solar date as varchar(10)    

--   ToGregorian ( @date ) : input solar date varchar(10), output datetime

-- SolarYear ( @date )   : input datetime , output solar year as smallint

-- SolarMonth ( @date )  : input datetime , output solar month as smallint

-- SolarDay ( @date )    : input datetime , output solar day as smallint

--

--  EXAMPLE:

--

-- select dbo.ToGregorian( '1389/5/1' )

-- select dbo.ToSolar( getdate() )

-- select dbo.SolarYear( getdate() )

-- select dbo.SolarMonth( getdate() )

-- select dbo.SolarDay( getdate() )

---------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------

CREATE FUNCTION [dbo].[LeapMonth] (@type SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array VARCHAR(5)

DECLARE @result SMALLINT

SET @array = '12,02' -- esfand, february

SET @result = CAST(SUBSTRING(@array, @type*2+@type+1, 2) AS SMALLINT)

RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysOfMonths] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array_s VARCHAR(38) -- shamsi month days

DECLARE @array_g VARCHAR(38) -- gregorian month days

DECLARE @result SMALLINT

SET @array_s = '00,31,31,31,31,31,31,30,30,30,30,30,29'

SET @array_g = '00,31,28,31,30,31,30,31,31,30,31,30,31'

IF ( @type = 0 )

 SET @result = CAST(SUBSTRING(@array_s, @month*2+@month+1, 2) AS SMALLINT)

ELSE

 SET @result = CAST(SUBSTRING(@array_g, @month*2+@month+1, 2) AS SMALLINT)



RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysToMonth] (@type SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @array_s VARCHAR(55) -- shamsi month days

DECLARE @array_g VARCHAR(55) -- gregorian month days

DECLARE @result SMALLINT

SET @array_s = '000,000,031,062,093,124,155,186,216,246,276,306,3  36,365'

SET @array_g = '000,000,031,059,090,120,151,181,212,243,273,304,3  34,365'

IF ( @type = 0 )

 SET @result = CAST(SUBSTRING(@array_s, @month*3+@month+1, 3) AS SMALLINT)

ELSE

 SET @result = CAST(SUBSTRING(@array_g, @month*3+@month+1, 3) AS SMALLINT)



RETURN @result

END

GO



CREATE FUNCTION [dbo].[IsLeapYear] (@type SMALLINT, @year SMALLINT)  

RETURNS BIT AS

BEGIN 

DECLARE @result BIT

IF ( @type = 0 )

BEGIN

 IF ( ((((@year + 38) * 31) % 128) <= 30) )

  SET @result = 1

 ELSE

  SET @result = 0

END

ELSE

BEGIN

 IF ( ((@year%4) = 0) and (((@year%100) != 0) or ((@year%400) = 0)) )

  SET @result = 1

 ELSE

  SET @result = 0

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[IsDateValid] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS BIT AS

BEGIN 

DECLARE @result BIT

IF ( (@year != 0) AND (@month >= 1) AND (@month <= 12) AND

         (@day >= 1) AND (@day <= dbo.DaysOfMonth(@type, @year, @month)) )

 SET @result = 1

ELSE

 SET @result = 0

RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysOfMonth] (@type SMALLINT, @year SMALLINT, @month SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @result SMALLINT

IF ( (@year != 0) AND (@month >= 1 AND @month <= 12) )

BEGIN

 SET @result = dbo.DaysOfMonths(@type, @month)

 IF ( (@month = dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year)=1) )

  SET @result = @result+1

END 

ELSE

 SET @result = 0



RETURN @result

END

GO



CREATE FUNCTION [dbo].[DaysToDate] (@type SMALLINT, @year SMALLINT, @month SMALLINT, @day SMALLINT)  

RETURNS SMALLINT AS

BEGIN 

DECLARE @result SMALLINT

IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1)

BEGIN

 SET @result = dbo.DaysToMonth(@type, @month) + @day

 IF ( (@month > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

  SET @result = @result+1

END

ELSE

 SET @result = 0

RETURN @result

END

GO



CREATE FUNCTION [dbo].[GregorianToSolar] (@year SMALLINT, @month SMALLINT, @day SMALLINT)

RETURNS VARCHAR(10) AS

BEGIN

DECLARE @result VARCHAR(10)

DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevgregorianleap BIT

SET @result = 'ERROR'

IF ( dbo.IsDateValid(1, @year, @month, @day) = 1 )

BEGIN

 SET @prevgregorianleap = dbo.IsLeapYear(1, @year-1)

 SET @days = dbo.DaysToDate(1, @year, @month, @day)

 SET @year = @year - 622

 IF ( dbo.IsLeapYear(0, @year) = 1 )

  SET @leapday = 1

 ELSE

  SET @leapday = 0

 IF ( (@prevgregorianleap = 1) AND (@leapday = 1) )

  SET @days = @days + 287

 ELSE

  SET @days = @days + 286

 IF ( @days > (365 + @leapday) )

 BEGIN

  SET @year = @year+1

  SET @days = @days - (365 + @leapday)

 END

 --

 -- EXEC dbo.DateOfDay 0, @days, @year, @month OUT, @day OUT, @result OUT

 -- insert procedure body for converting procedure to function, for using in query

 DECLARE @type SMALLINT

 DECLARE @m SMALLINT

 SET @type = 0

 SET @leapday = 0

 SET @month = 0

 SET @day = 0

 SET @m = 2

 WHILE (@m <= 13)

 BEGIN

  IF ( (@m > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

   SET @leapday = 1

  IF ( @days <= (dbo.DaysToMonth(@type, @m) + @leapday) )

  BEGIN

   SET @month = @m - 1

   IF ( @month <= dbo.LeapMonth(@type) ) 

    SET @leapday = 0

   SET @day = @days - (dbo.DaysToMonth(@type, @month) + @leapday)

   BREAK

  END

  SET @m = @m+1

 END

 IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1 )

  SET @result = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR) 

 -- End procedure

 --

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarToGregorian] (@year SMALLINT, @month SMALLINT, @day SMALLINT)

RETURNS DATETIME AS

BEGIN

DECLARE @result DATETIME

DECLARE @leapday SMALLINT

DECLARE @days SMALLINT

DECLARE @prevsolarleap BIT

SET @result = 0 -- Jan  1 1900 12:00AM

IF ( dbo.IsDateValid(0, @year, @month, @day) = 1 )

BEGIN

 SET @prevsolarleap= dbo.IsLeapYear(0, @year-1)

 SET @days = dbo.DaysToDate(0, @year, @month, @day)

 SET @year = @year + 621

 IF ( dbo.IsLeapYear(1, @year) = 1 )

  SET @leapday = 1

 ELSE

  SET @leapday = 0

 IF ( (@prevsolarleap = 1) AND (@leapday = 1) )

  SET @days = @days + 80

 ELSE

  SET @days = @days + 79

 IF ( @days > (365 + @leapday) )

 BEGIN

  SET @year = @year+1

  SET @days = @days - (365 + @leapday)

 END

 -- EXEC dbo.DateOfDay 1, @days, @year, @month OUT, @day OUT, @result OUT

 -- insert procedure body for converting procedure to function, for using in query

 DECLARE @type SMALLINT

 DECLARE @temp VARCHAR(10)

 DECLARE @m SMALLINT

 SET @type = 1 

 SET @leapday = 0

 SET @month = 0

 SET @day = 0

 SET @m = 2

 WHILE (@m <= 13)

 BEGIN

  IF ( (@m > dbo.LeapMonth(@type)) AND (dbo.IsLeapYear(@type, @year) = 1) )

   SET @leapday = 1

  IF ( @days <= (dbo.DaysToMonth(@type, @m) + @leapday) )

  BEGIN

   SET @month = @m - 1

   IF ( @month <= dbo.LeapMonth(@type) ) 

    SET @leapday = 0

   SET @day = @days - (dbo.DaysToMonth(@type, @month) + @leapday)

   BREAK

  END

  SET @m = @m+1

 END

 IF ( dbo.IsDateValid(@type, @year, @month, @day) = 1 )

 BEGIN

  SET @temp = CAST(@year AS VARCHAR) + '/' + CAST(@month AS VARCHAR) + '/' + CAST(@day AS VARCHAR)

  SET @result = CAST(@temp AS DATETIME) 

 END

 -- End procedure

 --

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[ToSolar] (@date DATETIME)

RETURNS VARCHAR(10) AS

BEGIN

DECLARE @result VARCHAR(10)

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

RETURN dbo.GregorianToSolar (@year, @month, @day)

END

GO



CREATE FUNCTION [dbo].[ToGregorian] (@date VARCHAR (10))

RETURNS DATETIME AS

BEGIN

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @pos1 = CHARINDEX('/', @date)

SET @pos2 = CHARINDEX('/', @date, @pos1+1)

SET @year = CAST(LEFT(@date, @pos1-1) AS SMALLINT)

SET @month = CAST(SUBSTRING(@date, @pos1+1, @pos2-@pos1-1) AS SMALLINT) 

SET @day = CAST(SUBSTRING(@date, @pos2+1, LEN(@date)-@pos2) AS SMALLINT)

RETURN dbo.SolarToGregorian (@year, @month, @day)

END

GO



CREATE FUNCTION [dbo].[SolarYear] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @result = CAST(LEFT(@temp, @pos1-1) AS SMALLINT)

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarMonth] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @pos2 = CHARINDEX('/', @temp, @pos1+1)

 SET @result = CAST(SUBSTRING(@temp, @pos1+1, @pos2-@pos1-1) AS SMALLINT) 

END

RETURN @result

END

GO



CREATE FUNCTION [dbo].[SolarDay] (@date DATETIME)

RETURNS SMALLINT AS

BEGIN

DECLARE @result SMALLINT

DECLARE @year SMALLINT

DECLARE @month SMALLINT

DECLARE @day SMALLINT

DECLARE @flag BIT

DECLARE @temp VARCHAR (10)

DECLARE @pos1 SMALLINT

DECLARE @pos2 SMALLINT

SET @result = 0

SET @year = DATEPART(yyyy, @date)

SET @month = DATEPART(mm, @date)

SET @day = DATEPART(dd, @date)

SET @temp = dbo.GregorianToSolar (@year, @month, @day)

IF ( @temp != 'ERROR' )

BEGIN

 SET @pos1 = CHARINDEX('/', @temp)

 SET @pos2 = CHARINDEX('/', @temp, @pos1+1)

 SET @result = CAST(SUBSTRING(@temp, @pos2+1, LEN(@temp)-@pos2) AS SMALLINT)

END

RETURN @result

END

GO

---

خوب چه طوربایداستفاده کرد
ممنون

نقل قول:

---

نوشته شده توسط rahele83

خوب چه طوربایداستفاده کرد
ممنون

---

خیلی ساده است کدهای ارسالی را بر روی هر database ای که می خواهید در آن استفاده کنید 1 بار اجرا کنید (توسط Query Analyazer/Managment Studio) تا توابع مورد نیاز در database ایجاد شوند، راهنما را هم که در ابتدای کد گذاشتم.

مثال انتخاب فیلد فرضی تاریخ (RegDate) و تبدیل آن از جدول فرضی MyTable:

SELECT dbo.ToSolar(MyTable.[RegDate]) FROM [MyTable]

نقل قول:

---

نوشته شده توسط sasan_vm

خیلی ساده است کدهای ارسالی را بر روی هر database ای که می خواهید در آن استفاده کنید 1 بار اجرا کنید (توسط Query Analyazer/Managment Studio) تا توابع مورد نیاز در database ایجاد شوند، راهنما را هم که در ابتدای کد گذاشتم.

مثال انتخاب فیلد فرضی تاریخ (RegDate) و تبدیل آن از جدول فرضی MyTable:

SELECT dbo.ToSolar(MyTable.[RegDate]) FROM [MyTable]

---

ممنون دوست عزیز پیغام زیر مربوط به چیه؟
'ToSolar' is not a recognized built-in function name.
با تشکر

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

سلام
ببخشید من از توابع شما استفاده کردم اما اگر بخواهم در همه جا تاریخ رو به صورت شمسی نمایش دهد باید برای تک تک فیلدهای موجود در جدول های مختلفی که دارم دستور بنویسم و همچنین چطوری در برنامه در همه زمان ها از این ها استفاده کنم ؟

دوستان سلام هیچ راه حلی به ذهنتون در این موردآخر نمی رسه؟