# پایگاه‌های داده > SQL Server >  تبدیل تاریخ میلادی به شمسی و بالعکس

## sasan_vm

سلام
نوشتن این کد چند ساعت وقت گرفت، برای استفاده دوستان 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

----------


## 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

> در این نسخه 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
> --   
> ...


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

----------


## sasan_vm

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


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

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


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

----------


## MOJTABAATEFEH

> خیلی ساده است کدهای ارسالی را بر روی هر 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 استفاده کنید.

----------


## khaterer

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

----------


## khaterer

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

----------

