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