Skipping the day of the week to get the closest date in SQL

Question

I am working on a query that matters frequency (i.e. Mondays, Tuesdays, etc. - think of appointments).

So, in my query, I got the result

jobId:1, personId:100, frequencyVal: 'Mondays' jobId:2, personId:101, frequencyVal: 'Saturdays' 

I need the following 4 future (or current) dates for frequencyVal.

So, if today is 1/3/2015

I need my result set to be

 jobId:1, personId:100, frequencyVal: 'Mondays', futureDates: '1/5,1/12,1/19,1/26' jobId:2, personId:102, frequencyVal: 'Saturdays', futureDates: '1/3,1/10,1/17,1/24' 

I watched the following entry: How to find the nearest (day of the week) on a given date

But that sets it for a specific date. And I see this as a web application, and I need dates for the current date. Therefore, if I try to run this query next Tuesday, future dates for jobId:1 would remove the 1/5 and add the 2/2 .

Is there a way to pass the value of the day of the week to get the next nearest date?

Answer 1

Your sample table

 create table #t ( jobId int, personId int, frequencyVal varchar(10) ); insert into #t values (1,100,'Mondays'),(2,101,'Saturdays'); 

QUERY 1: select the next 4 weeks of the days in the current month for a specific day of the week

 -- Gets first day of month DECLARE @FIRSTDAY DATE=DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) ;WITH CTE as ( -- Will find all dates in current month SELECT CAST(@FIRSTDAY AS DATE) as DATES UNION ALL SELECT DATEADD(DAY,1,DATES) FROM CTE WHERE DATES < DATEADD(MONTH,1,@FIRSTDAY) ) ,CTE2 AS ( -- Join the #t table with CTE on the datename+'s' SELECT jobId,personId,frequencyVal,DATES, -- Get week difference for each weekday DATEDIFF(WEEK,DATES,GETDATE()) WEEKDIFF, -- Count the number of weekdays in a month COUNT(DATES) OVER(PARTITION BY DATENAME(WEEKDAY,CTE.DATES)) WEEKCOUNT FROM CTE JOIN #t ON DATENAME(WEEKDAY,CTE.DATES)+'s' = #t.frequencyVal WHERE MONTH(DATES)= MONTH(GETDATE()) ) -- Converts to CSV and make sure that only nearest 4 week of days are generated for month SELECT DISTINCT C2.jobId,C2.personId,frequencyVal, SUBSTRING( (SELECT ', ' + CAST(DATEPART(MONTH,DATES) AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY,DATES) AS VARCHAR(2)) FROM CTE2 WHERE C2.jobId=jobId AND C2.personId=personId AND C2.frequencyVal=frequencyVal AND ((WEEKDIFF<3 AND WEEKDIFF>-3 AND WEEKCOUNT = 5) OR WEEKCOUNT <= 4) ORDER BY CTE2.DATES FOR XML PATH('')),2,200000) futureDates FROM CTE2 C2 

• SQL FIDDLE

For example, in Query2, the nearest date (here we take the example as Saturday)

 2015-Jan-10 will be 01/03,01/10,01/17,01/24 2015-Jan-24 will be 01/10,01/17,01/24,01/31 

QUERY 2: Select the following 4-week dates for a specific day of the week that are not related to the month

 ;WITH CTE as ( -- Will find the next 4 week details SELECT CAST(GETDATE() AS DATE) as DATES UNION ALL SELECT DATEADD(DAY,1,DATES) FROM CTE WHERE DATES < DATEADD(DAY,28,GETDATE()) ) ,CTE2 AS ( -- Join the #t table with CTE on the datename+'s' SELECT jobId,personId,frequencyVal, DATES, ROW_NUMBER() OVER(PARTITION BY DATENAME(WEEKDAY,CTE.DATES) ORDER BY CTE.DATES) DATECNT FROM CTE JOIN #t ON DATENAME(WEEKDAY,CTE.DATES)+'s' = #t.frequencyVal ) -- Converts to CSV and make sure that only 4 days are generated for month SELECT DISTINCT C2.jobId,C2.personId,frequencyVal, SUBSTRING( (SELECT ', ' + CAST(DATEPART(MONTH,DATES) AS VARCHAR(2)) + '/' + CAST(DATEPART(DAY,DATES) AS VARCHAR(2)) FROM CTE2 WHERE C2.jobId=jobId AND C2.personId=personId AND C2.frequencyVal=frequencyVal AND DATECNT < 5 ORDER BY CTE2.DATES FOR XML PATH('')),2,200000) futureDates FROM CTE2 C2 

• SQL FIDDLE

The following will be the conclusion if GETDATE() (if its Saturday)

 2015-01-05 - 1/10, 1/17, 1/24, 1/31 2015-01-24 - 1/24, 1/31, 2/7, 2/14 

Answer 2

I prefer a calendar table for this type of query. In fact, I prefer to use a calendar table over date functions for most queries. Here is minimal. I use more columns and more rows in production. (100 years of data - only 37 thousand lines).

 create table calendar ( cal_date date not null primary key, day_of_week varchar(15) ); insert into calendar (cal_date) values ('2015-01-01'), ('2015-01-02'), ('2015-01-03'), ('2015-01-04'), ('2015-01-05'), ('2015-01-06'), ('2015-01-07'), ('2015-01-08'), ('2015-01-09'), ('2015-01-10'), ('2015-01-11'), ('2015-01-12'), ('2015-01-13'), ('2015-01-14'), ('2015-01-15'), ('2015-01-16'), ('2015-01-17'), ('2015-01-18'), ('2015-01-19'), ('2015-01-20'), ('2015-01-21'), ('2015-01-22'), ('2015-01-23'), ('2015-01-24'), ('2015-01-25'), ('2015-01-26'), ('2015-01-27'), ('2015-01-28'), ('2015-01-29'), ('2015-01-30'), ('2015-01-31'), ('2015-02-01'), ('2015-02-02'), ('2015-02-03'), ('2015-02-04'), ('2015-02-05'), ('2015-02-06'), ('2015-02-07'), ('2015-02-08'), ('2015-02-09'), ('2015-02-10'), ('2015-02-11'), ('2015-02-12'), ('2015-02-13'), ('2015-02-14'), ('2015-02-15'), ('2015-02-16'), ('2015-02-17'), ('2015-02-18'), ('2015-02-19'), ('2015-02-20'), ('2015-02-21'), ('2015-02-22'), ('2015-02-23'), ('2015-02-24'), ('2015-02-25'), ('2015-02-26'), ('2015-02-27'), ('2015-02-28') ; update calendar set day_of_week = datename(weekday, cal_date); alter table calendar alter column day_of_week varchar(15) not null; alter table calendar add constraint cal_date_matches_dow check (datename(weekday, cal_date) = day_of_week); create index day_of_week_ix on calendar (day_of_week); 

Set privileges so that

• everyone can choose but
• almost no one can insert new lines, and
• even fewer people can delete lines.

(Or write a constraint that can guarantee no spaces. I think you can do it in SQL Server.)

You can select the next four Mondays after today with a very simple SQL statement. (The current date is 2015-01-05, which is Monday.)

 select top 4 cal_date from calendar where cal_date > convert(date, getdate()) and day_of_week = 'Monday' order by cal_date; 

  CAL_DATE
 -
 2015-01-12
 2015-01-19
 2015-01-26
 2015-02-02

This is a huge advantage for me. No procedural code. Simple SQL, which is obviously right. Big win.

Answer 3

There is no built-in function. But you can try this, you can place it inside the Scalar-Valued Function:

 DECLARE @WeekDay VARCHAR(10) = 'Monday'; DECLARE @WeekDayInt INT; SELECT @WeekDayInt = CASE @WeekDay WHEN 'SUNDAY' THEN 1 WHEN 'MONDAY' THEN 2 WHEN 'TUESDAY' THEN 3 WHEN 'WEDNESDAY' THEN 4 WHEN 'THURSDAY' THEN 5 WHEN 'FRIDAY' THEN 6 WHEN 'SATURDAY' THEN 7 END SELECT CONVERT(DATE, DATEADD(DAY, (DATEPART(WEEKDAY, GETDATE()) + @WeekDayInt) % 7, GETDATE())) AS NearestDate 

UPDATE:

It seems that the radar was right, here is the solution:

 DECLARE @WeekDay VARCHAR(10) = 'Monday'; DECLARE @WeekDayInt INT; DECLARE @Date DATETIME = GETDATE(); SELECT @WeekDayInt = CASE @WeekDay WHEN 'SUNDAY' THEN 1 WHEN 'MONDAY' THEN 2 WHEN 'TUESDAY' THEN 3 WHEN 'WEDNESDAY' THEN 4 WHEN 'THURSDAY' THEN 5 WHEN 'FRIDAY' THEN 6 WHEN 'SATURDAY' THEN 7 END DECLARE @Diff INT = DATEPART(WEEKDAY, @Date) - @WeekDayInt; SELECT CONVERT(DATE, DATEADD(DAY, CASE WHEN @Diff >= 0 THEN 7 - @Diff ELSE ABS(@Diff) END, @Date)) AS NearestDate 

Answer 4

Try this - based on king.code's answer to get the nearest date.

 create table #t ( jobId int, personId int, frequencyVal varchar(10) ); insert into #t values (1,100,'Mondays'),(2,101,'Saturdays'); WITH cte(n) AS ( SELECT 0 UNION ALL SELECT n+1 FROM cte WHERE n < 3 ) select #t.jobId, #t.personId, #t.frequencyVal, STUFF(ad, 1, 1, '') AS FutureDates from #t cross apply (SELECT CASE #t.frequencyVal WHEN 'SUNDAYS' THEN 1 WHEN 'MONDAYS' THEN 2 WHEN 'TUESDAYS' THEN 3 WHEN 'WEDNESDAYS' THEN 4 WHEN 'THURSDAYS' THEN 5 WHEN 'FRIDAYS' THEN 6 WHEN 'SATURDAYS' THEN 7 END)tranlationWeekdays(n) cross apply (select ',' + CONVERT(varchar(10), CONVERT(date,dateadd(WEEK, cte.n,CONVERT(DATE, DATEADD(DAY, (DATEPART(WEEKDAY, GETDATE()) + tranlationWeekdays.n) % 7, GETDATE()))))) from cte FOR XML PATH('')) a(d); drop table #t; 

Answer 5

This is an easier way, I think, and I think it meets your requirements.

Notice that I changed your frequency_val column to an integer representing the day of the week from the perspective of SQL servers, and added a calculated column to illustrate how you can easily get the day name from this.

 declare @t table ( jobId int, personId int, --frequencyVal varchar(10) frequency_val int, frequency_day as datename(weekday,frequency_val -1) + 's' ); 

declare @num_occurances int = 4 declare @from_date date = dateadd(dd,3,getdate()) -- this will allow you to play with the date simply by changing the increment value

insert into @t values (1,100,1),--'Mondays'), (2,101,6),--'Saturdays'); (3,101,7),--'Saturdays'); (4,100,2)--'Mondays'), --select * from @t

;with r_cte (days_ahead, occurance_date) as (select 0, convert(date,@from_date,121) union all select r_cte.days_ahead +1, convert(date,dateadd(DD, r_cte.days_ahead+1, @from_date),121) from r_cte where r_cte.days_ahead < 7 * @num_occurances ) select t.*, r_cte.occurance_date from @tt inner join r_cte on DATEPART(WEEKDAY, dateadd(dd,@@DATEFIRST - 1 ,r_cte.occurance_date)) = t.frequency_val

Answer 6

Try it,

 DECLARE @YEAR INT=2015 DECLARE @MONTH INT=1 DECLARE @DAY INT=1 DECLARE @DATE DATE = (SELECT DateFromParts(@Year, @Month, @Day)) DECLARE @TOTAL_DAYS INT =(SELECT DatePart(DY, @DATE)); WITH CTE1 AS (SELECT T_DAY=(SELECT DateName(DW, @DATE)), @DATE AS T_DATE, @DAY AS T_DDAY UNION ALL SELECT T_DAY=(SELECT DateName(DW, DateAdd(DAY, T_DDAY + 1, @DATE))), DateAdd(DAY, T_DDAY + 1, @DATE) AS T_DATE, T_DDAY + 1 FROM CTE1 WHERE T_DDAY + 1 <= 364) SELECT DISTINCT T_DAY, Stuff((SELECT ',' + CONVERT(VARCHAR(30), T_DATE) FROM CTE1 A WHERE A.T_DAY=CTE1.T_DAY AND A.T_DATE > GetDate() AND A.T_DATE<(DATEADD(WEEK,4,GETDATE())) FOR XML PATH('')), 1, 1, '') AS FUTURE FROM CTE1 ORDER BY T_DAY OPTION (MAXRECURSION 365) 

Answer 7

Having seen the use of DATENAME in some of the answers already received, I would like to indicate that the returned DATENAME values ​​may vary depending on your current language , but you can keep the current language setting and ensure that us_english used us_english that you can be sure to use English weekday names days.

Now here is my slightly different approach to get the 4 following dates that fall on a specific (known) weekday using a user-defined table function that allows you to create a table with a numerical sequence (yes, this is a pretty dull function, you have to pass MaxValue more than MinValue but it can be easily enhanced if necessary, but hey, it does the job). Using this function covers a table of more than 28 values ​​(the next 28 days should include the following 4 corresponding weekdays;)), apply DATEADD to GETDATE and reduce the result set with WHERE to the values ​​that have the right day of the week:

 CREATE FUNCTION GetIntSequence(@MinValue INT, @MaxValue INT) RETURNS @retSequence TABLE ( IntValue INT NOT NULL ) BEGIN DECLARE @i INT = (SELECT @MinValue) WHILE @i <= @MaxValue BEGIN INSERT INTO @retSequence (IntValue) SELECT @i SELECT @i = @i + 1 END RETURN END GO DECLARE @weekDay NVARCHAR(MAX) = 'Monday' --(or Tuesday, wednesday, ...) --save current language setting DECLARE @languageBackup NVARCHAR(MAX) = (SELECT @@LANGUAGE) --ensure us english language setting for reliable weekday names SET LANGUAGE us_english; SELECT FourWeeks.SomeDay FROM ( SELECT DATEADD(DAY, IntValue, GETDATE()) AS SomeDay FROM dbo.GetIntSequence(1, 28) ) AS FourWeeks WHERE DATENAME(WEEKDAY, SomeDay) = @weekDay --restore old language setting SET LANGUAGE @languageBackup; GO DROP FUNCTION dbo.GetIntSequence