Return date of following past week or future by 3 input arguments
-
Return a date (This is a calculated date based on the input arguments) It need to Take 3 arguments
Starting Date (Date) (Example 10/10/2005)
Increment (integer) (Example è +4 or -3)
Day of the Week (Varchar)
(Example Monday or Thursday)
Example: If I need to calculate the 5th Monday after 10/10/2005 I will call the function like this è
Exec fnCalculateNewDate @startDate= ‘10/10/2005’, @Increment=+5, @DayOfTheWeek=’Monday’
If I need to calculate 4th Sunday before ‘10/10/2005, I will call the function line this
Exec fnCalculateNewDate @startDate= ‘10/10/2005’, @Increment=-4, @DayOfTheWeek=’Sunday’
-
Can it take the SQL weekday values instead of VARCHAR.
6 instead of Friday?
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
no, The program has to use varchar unfortunately..
-
Too bad baceuse I think I have something that can do it with the number so you will just have to convert the VARCHAR to proper number yourself
Please have a look at this code and this should give you at least the starting point. I think it works correctly assuming I understand your reqs properly. You did not specify what to do when the increment is 0. I am using getdate() just to test it but you can replace this with your date.
DECLARE @dayofweek AS INT
DECLARE @increment AS INT
SET @dayofweek = 7
SET @increment = -1
IF @increment < 0 AND @dayofweek <> DATEPART(weekday, getdate())
SELECT @increment = @increment + 1
SELECT @increment =
CASE
WHEN @dayofweek <= DATEPART(weekday, getdate()) THEN @increment - 1
ELSE @increment - 2
END
SELECT DATEADD(week, @increment, DATEADD(day, @dayofweek - 1, (DATEADD(day, (8 - DATEPART(weekday, getdate())) % 7, getdate()))))
Have fun with this. I hope you can convert this to the function yourself.
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
You could use the UDF shown below to convert the day-of-week name into a number, or, if you can validate @DayOfTheWeek, just use the expression:
(CharIndex( Left(@DayOfTheWeek,3) , 'SunMonTueWedThuFriSat' ) / 3) + 1
GO
CREATE FUNCTION dbo.DayNumber
(
@dayName varchar(9)
)
RETURNS tinyint
AS
BEGIN
DECLARE @pos int
SET @pos = CharIndex( Left(@dayname,3) , 'SunMonTueWedThuFriSat' )
IF @pos > 0
RETURN (@pos / 3) + 1
RETURN 0
END
-
Here's a quick shot at the entire function:
-- SELECT dbo.fnCalculateNewDate('10/10/2005', -4, 'Sunday')
GO
DROP FUNCTION dbo.fnCalculateNewDate
GO
CREATE FUNCTION dbo.fnCalculateNewDate
(
@startDate datetime
, @Increment int
, @DayOfTheWeek varchar(9)
)
RETURNS datetime
AS
BEGIN
DECLARE @pos int
, @oldDayNumber int
, @dayNumber int
, @newDate datetime
-------------------------------------------------------------
-- First, convert the day name into a day-of-week number
-- Assume Sunday = 1 and Saturday = 7
-------------------------------------------------------------
SET @pos = CharIndex( Left(@DayOfTheWeek,3) , 'SunMonTueWedThuFriSat' )
IF @pos > 0
BEGIN
SET @dayNumber = (@pos / 3) + 1
-------------------------------------------------------------
-- Next, adjust the @startDate parameter to the day of
-- week that was specfied, then add the number of weeks
-- as requested via the @Increment parameter.
-------------------------------------------------------------
SET @startDate = DateAdd(d, @dayNumber - DatePart(dw, @startDate), @startDate)
SET @newDate = DateAdd(wk, @Increment, @startDate)
-- or
-- SET @newDate = DateAdd(wk, @Increment, DateAdd(d, @dayNumber - DatePart(dw, @startDate), @startDate))
END
ELSE
BEGIN
-- invalid DOW, return original date, or whatever you want to indicate an error
SET @newDate = @startDate
END
RETURN @newDate
END
GO
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply