Add one day to the date entered

About FuncAddDay (SQL User Defined Function)


1) It adds one day to the date entered.
2) It does recognize leap year and short months.
3) The input date must be in in MMDDYYYY or MM/DD/YYYY format.
4) It calls another user defined function FuncDateMMDDYYYY.

Function FuncAddDay

CREATE FUNCTION dbo.FuncAddDay(@Date as varchar(10)) RETURNS varChar(10)
AS
BEGIN

Declare @NewDate as varChar(10)
Declare @NewMonth as varchar(2)
Declare @NewYear as varchar(4)
Declare @NewDay as varchar(2)
Declare @NewTest as varchar(10)
Declare @AddNewDay as int
if substring(@Date,3,1) <> '/' set @Date = dbo.FuncDateMMDDYYYY(@Date)

set @NewDay = substring(@Date,4,2) + 1
set @NewMonth = substring(@Date,1,2)
set @NewYear = substring(@Date,7,4)

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

Return (@NewDate)

END

Function FuncDateMMDDYYYY
/* Converts date from mmddyyyy format to mm/dd/yyyy only if data is in mmddyyyy format */

CREATE FUNCTION dbo.FuncDateMMDDYYYY (@Date as varchar(10)) RETURNS varChar(10)
AS



BEGIN
Declare @NewDate as varChar(10)
if substring(@Date,3,1) = '/'
set @NewDate = @Date
else
Set @NewDate = SUBSTRING(@Date,1,2) + '/' + SUBSTRING(@Date,3,2) + '/' + SUBSTRING(@Date,5,4)

Return (@NewDate)
END

USAGE

After creating the above two functions call FuncAddDay with input date.

Example:

dbo.FuncAddDay('06/08/2009') will return 06/09/2009