Monday, June 29, 2009

Add x days to the date inputed and compare with current date

[code]
CREATE FUNCTION dbo.FuncAddDaysToCurrentDate(@BeginDate as varchar(8), @Number as int) RETURNS varChar(10)
AS
BEGIN

Declare @Return as varchar(1)
Declare @NewDate as varChar(10)
Declare @NewMonth as varchar(2)
Declare @NewYear as varchar(4)
Declare @NewDay as varchar(4)
Declare @NewTest as varchar(10)
Declare @AddNewDay as int
Declare @Date as varchar(11)
Declare @Month as varchar(3)
Declare @Day as varchar(2)
Declare @Year as varchar(4)
Declare @x as int

Set @Date = (Select GetTheDate from qryGetDate)
Set @Month = substring(@Date,1,3)
Set @Day = substring(@Date,5,2)
set @Year = substring(@Date,8,4)
if @Month = 'Jan' set @NewMonth = '01'
if @Month = 'Feb' set @NewMonth = '02'
if @Month = 'Mar' set @NewMonth = '03'
if @Month = 'Apr' set @NewMonth = '04'
if @Month = 'May' set @NewMonth = '05'
if @Month = 'Jun' set @NewMonth = '06'
if @Month = 'Jul' set @NewMonth = '07'
if @Month = 'Aug' set @NewMonth = '08'
if @Month = 'Sep' set @NewMonth = '09'
if @Month = 'Oct' set @NewMonth = '10'
if @Month = 'Nov' set @NewMonth = '11'
if @Month = 'Dec' set @NewMonth = '12'

set @NewDay = convert(int,@Day)
set @NewYear = @Year
set @x = 1

while (@x <= @Number) begin
set @NewDay = convert(int,@NewDay) + 1
set @NewTest = ''
if @Date <> '12/31/9999' begin
if (@NewMonth = '04' or @NewMonth = '06' or @NewMonth = '09' or @NewMonth = '11') and convert(int,@NewDay) > 30 begin
set @NewDay = '01'
set @NewMonth = convert(int,@NewMonth) + 1
if len(@NewMonth) = 1 set @NewMonth = '0' + @NewMonth
end
if (@NewYear % 4) = 0 and ((@NewYear % 100) <> 0 or (@NewYear % 400) = 0) set @NewTest = 'LeapYear'
if convert(int,@NewDay) > 28 and @NewMonth = '02' and @NewTest <> 'LeapYear' begin
set @NewDay = '01'
set @NewMonth = '03'
end
if convert(int,@NewDay) > 29 and @NewMonth = '02' and @NewTest = 'LeapYear' begin
set @NewDay = '01'
set @NewMonth = '03'
end
if convert(int,@NewDay) > 31 and @NewMonth <> '12' begin
set @NewDay = '01'
set @NewMonth = convert(int,@NewMonth) + 1
if len(@NewMonth) = 1 set @NewMonth = '0' + @NewMonth
end
if convert(int,@NewDay) > 31 and @NewMonth = '12' begin
set @NewDay = '01'
set @NewMonth = '01'
set @NewYear = convert(int,@NewYear) + 1
end
end
set @x = @x + 1
end

if @Date = '12/31/9999' set @NewDate = '12/31/9999'

if len(@NewDay) = 1 set @NewDay = '0' + @NewDay
if len(@NewMonth) = 1 set @NewMonth = '0' + @NewMonth
set @NewDate = @NewYear + @NewMonth + @NewDay
if @BeginDate >= @NewDate set @Return = 1 else set @Return = 0

Return (@Return)

END
[/code]


Add x days to the date entered. If the date specified with X days added is greater than or equal to the current date, it will return 1 else 0

Eg: FuncAddDaysToCurrentDate('1/1/2009',10) would return 0

No comments:

Post a Comment