PDA

View Full Version : تبدیل تاریخ میلادی به شمسی و بالعکس


sasan_vm
جمعه 01 مرداد 1389, 15:05 عصر
سلام
نوشتن این کد چند ساعت وقت گرفت، برای استفاده دوستان 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
جمعه 01 مرداد 1389, 16:30 عصر
در این نسخه 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
جمعه 01 مرداد 1389, 23:47 عصر
در این نسخه 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
شنبه 02 مرداد 1389, 10:02 صبح
خوب چه طوربایداستفاده کرد
ممنون

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

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



SELECT dbo.ToSolar(MyTable.[RegDate]) FROM [MyTable]
MOJTABAATEFEH
چهارشنبه 08 دی 1389, 22:39 عصر
خیلی ساده است کدهای ارسالی را بر روی هر 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.
با تشکر
حمیدرضاصادقیان
پنج شنبه 09 دی 1389, 09:05 صبح
این پیغام به خاطر این هست که احتمالا تابع مربوطه رو شما نساختین.
یکبار دیگه Script بالا رو اجرا کنید بعد از این Select استفاده کنید.
khaterer
یک شنبه 26 دی 1389, 16:00 عصر
سلام
ببخشید من از توابع شما استفاده کردم اما اگر بخواهم در همه جا تاریخ رو به صورت شمسی نمایش دهد باید برای تک تک فیلدهای موجود در جدول های مختلفی که دارم دستور بنویسم و همچنین چطوری در برنامه در همه زمان ها از این ها استفاده کنم ؟
khaterer
پنج شنبه 30 دی 1389, 14:27 عصر
دوستان سلام هیچ راه حلی به ذهنتون در این موردآخر نمی رسه؟