November 29, 2011 at 9:33 am
Hi all,
Been trying to figure out a decent way of getting the next specified day of the week from a startdate.
Easy enough if the specified day is on or after the startdate but if I wanted the next monday from a startdate that is a tuesday my generalised function doesnt work and gives me the date of the Monday just gone (yesterday).
I'm sure I am being dense and this is not difficult so can anyone put me out of my misery.
Rolf
November 30, 2011 at 12:33 am
Hello Rolf,
maybe this way:
DECLARE @startdate DATE;
DECLARE @daytofind INT;
SET @startdate = '2011-11-29';
SET @daytofind = 2;
WITH myWeek AS
(SELECT @startdate AS myDay, DATEPART(WEEKDAY, @startdate) as MyDayofWeek
UNION ALL
SELECT DATEADD(dd,1,myDay) AS myDay, DATEPART(WEEKDAY, DATEADD(dd,1,myDay)) as MyDayofWeek FROM myWeek WHERE DATEDIFF(dd,@startdate,myday) < 6)
SELECT myDay FROM myWeek WHERE MyDayofWeek = @daytofind
you can change the root element of the recursion to a DATEADD if you don't want to include the startdate in the search
Lars
November 30, 2011 at 1:01 am
OK, sorry, just realized that this is the subforum for 2000, so the query above wouldn't work, cte is a feature of 2005 and above...
🙁
I'm just starting to find my way around here in this community, I'll try to read more carefully next time before posting!
Lars
November 30, 2011 at 7:40 am
There's probably a way to do this with just a DATEADD statement, but off the top of my head I can't think of one.
Anyway, this should work: -
DECLARE @startdate DATETIME
SET @startdate = '2011-11-30'
SELECT CASE WHEN DATEDIFF(dd,0,@startdate) %7 = 5
THEN DATEADD(DAY,2,@startdate)
WHEN DATEDIFF(dd,0,@startdate) %7 = 4
THEN DATEADD(DAY,3,@startdate)
ELSE DATEADD(DAY,1,@startdate) END
Although syntactically no different, the below looks a little better than the above.
DECLARE @startdate DATETIME
SET @startdate = '2011-11-18';
SELECT DATEADD(DAY,CASE WHEN DATEDIFF(dd,0,@startdate) %7 = 5
THEN 2
WHEN DATEDIFF(dd,0,@startdate) %7 = 4
THEN 3
ELSE 1 END,@startdate)
November 30, 2011 at 7:44 am
Yes I've been scratching my head looking for a simple one liner dateadd statement that will get it but I my feeble brain cant work it out so I have ended up with some sort of choice/logic in there using either CASE, WHERE or IF in the T-SQL.
Thanks for the input....can any boffins out there improve on this..?
Rolf
November 30, 2011 at 8:20 am
Another method -
DECLARE @startdate DATETIME
SET @startdate = '2011-11-18'
SELECT TOP 1 DATEADD(dd, Num, DATEDIFF(dd, 0, @startdate))
FROM (SELECT 1 AS Num UNION ALL SELECT 2 UNION ALL SELECT 3) AS a
WHERE (DATEDIFF(dd, 0, @startdate) + Num) % 7 <= 4
ORDER BY DATEADD(dd, Num, DATEDIFF(dd, 0, @startdate))
And another -
DECLARE @startdate DATETIME
SET @startdate = '2011-11-18'
SELECT DATEADD(dd, a.ID, DATEDIFF(dd, 0, @startdate))
FROM (SELECT 1 AS ID, (DATEDIFF(dd, 0, @startdate) + 1) % 7 AS checker
UNION ALL SELECT 2, (DATEDIFF(dd, 0, @startdate) + 2) % 7
UNION ALL SELECT 3, (DATEDIFF(dd, 0, @startdate) + 3) % 7) a
LEFT OUTER JOIN (SELECT 1 AS ID, (DATEDIFF(dd, 0, @startdate) + 1) % 7 AS checker
UNION ALL SELECT 2, (DATEDIFF(dd, 0, @startdate) + 2) % 7
UNION ALL SELECT 3, (DATEDIFF(dd, 0, @startdate) + 3) % 7) b ON a.checker > b.checker
WHERE b.ID IS NULL
November 30, 2011 at 10:00 am
And another, far uglier method 😀
DECLARE @startdate DATETIME
SET @startdate = '2011-11-18'
SELECT DATEADD(DAY,ISNULL(NULLIF(ISNULL(NULLIF(ISNULL(NULLIF(NULLIF(NULLIF(NULLIF(NULLIF(DATEDIFF(dd,0,@startdate)%7,0),1),2),3),6),1),4),3),5),2), @startdate)
December 8, 2011 at 6:38 pm
DECLARE @NeededDW tinyint, @AfterDate datetime
SET @NeededDW = 5
SET @AfterDate = GETDATE()
select MIN(Date)
from Tally
WHERE Date > @AfterDate
AND DayOfWeek = @NeededDW
_____________
Code for TallyGenerator
December 10, 2011 at 8:23 am
Declare @NextDOW Int,@Date SmalldateTime
Set @NextDOW=2
Set @Date='20111205'
Select dateadd(dd,-datediff(dd,6+@NextDOW,@Date)%7+7,@Date)
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply