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