نمایش نتایج 1 تا 8 از 8

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

  1. #1
    کاربر دائمی آواتار sasan_vm
    تاریخ عضویت
    بهمن 1381
    محل زندگی
    زاهدان
    پست
    494

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

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

  2. #2
    کاربر دائمی آواتار sasan_vm
    تاریخ عضویت
    بهمن 1381
    محل زندگی
    زاهدان
    پست
    494

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

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

  3. #3

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

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

  4. #4
    کاربر دائمی آواتار sasan_vm
    تاریخ عضویت
    بهمن 1381
    محل زندگی
    زاهدان
    پست
    494

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

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

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


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

  5. #5

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

    نقل قول نوشته شده توسط 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.
    با تشکر

  6. #6

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

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

  7. #7

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

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

  8. #8

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

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

برچسب های این تاپیک

قوانین ایجاد تاپیک در تالار

  • شما نمی توانید تاپیک جدید ایجاد کنید
  • شما نمی توانید به تاپیک ها پاسخ دهید
  • شما نمی توانید ضمیمه ارسال کنید
  • شما نمی توانید پاسخ هایتان را ویرایش کنید
  •