SQL Query for getting nth Weekday date between two dates

Question

My scenario is as below:

@StartDate = 13th of current month
@EndDate = 12th of next month.

I want to get all the date with the day-name for Mondays, Tuesdays, Wednesdays, Thursdays, Fridays, Saturdays and Sundays lying between the start and end date.

Possible duplicate of [How to list all dates between two dates](http://stackoverflow.com/questions/17529860/how-to-list-all-dates-between-two-dates) — Cosmin, Jan 14 '16 at 10:19
I want to know how many mondays, tuesdays.....Sundays are occurring with the respective dates. — rp4361, Jan 14 '16 at 10:23
Ok. It seems it's a bit different than the link provided by me. I posted an answer. Maybe that's what you need. Have a nice day ! — Cosmin, Jan 14 '16 at 11:08

score 4 · Answer 1 · answered Jan 14 '16 at 10:22

Try this:

declare @startDate datetime = '2016-01-13'
declare @endDate datetime = '2016-02-12'

;with dateRange as
(
  select [Date] = dateadd(dd, 1, @startDate)
  where dateadd(dd, 1, @startDate) < @endDate
  union all
  select dateadd(dd, 1, [Date])
  from dateRange
  where dateadd(dd, 1, [Date]) < @endDate
)

select [Date], datename(dw,[Date])
from dateRange

score 0 · Answer 2 · answered Jan 14 '16 at 10:45

To count the number of each day as per your comment (this should be part of the question really), change the last part of James' answer to this:

select datename(dw,[Date]) as day_name, count([Date]) as number_days
from dateRange group by datename(dw,[Date]), datepart(DW,[Date])
order by datepart(DW,[Date]);

score 0 · Answer 3 · answered Jan 14 '16 at 11:01

You can try something like this :

DECLARE @StartDate DATETIME
DECLARE @StartDateFixed DATETIME
DECLARE @EndDate DATETIME
DECLARE @NumberOfDays int


SET @StartDate = '2016/01/01'
SET @EndDate = '2016/01/02'
SET @NumberOfDays = DATEDIFF(DAY,@StartDate,@EndDate) + 1 

SET @StartDateFixed = DATEADD(DD,-1,@StartDate)

SELECT  WeekDay , COUNT(WeekDay) 
FROM  (
        SELECT TOP (@NumberOfDays) WeekDay = DATENAME(DW , DATEADD(DAY,ROW_NUMBER() OVER(ORDER BY spt.name), @StartDateFixed))    
        FROM   [master].[dbo].[spt_values] spt 
      ) A
GROUP BY WeekDay

The output will be

WeekDay                        
------------------------------ -----------
Friday                         1
Saturday                       1

(2 row(s) affected)

score 0 · Answer 4 · answered Jan 14 '16 at 11:05

In case if you need to get current and next date from date number specified such as 13 and 12

Current Month

DECLARE @cur_mont INT =  (SELECT MONTH(GETDATE()))

Current Year

DECLARE @cur_year INT = (SELECT YEAR(GETDATE()))

Next Month

DECLARE @nxt_mont INT = (SELECT MONTH(DATEADD(month, 1, GETDATE())))

Next Month year (In case of December year change)

DECLARE @nxt_year INT = (SELECT YEAR(DATEADD(month, 1, GETDATE())))

Create start date

DECLARE @startDate DATETIME = (SELECT CAST(CAST(@cur_year AS varchar) + '-' + CAST(@cur_mont AS varchar) + '-' + CAST(13 AS varchar) AS DATETIME))

Create end date

DECLARE @endDate DATETIME = (SELECT CAST(CAST(@nxt_year AS varchar) + '-' + CAST(@nxt_mont AS varchar) + '-' + CAST(12 AS varchar) AS DATETIME))

Dates between start and end date

SELECT  TOP (DATEDIFF(DAY, @startDate, @endDate) + 1)
    DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate) AS Date,
    DATENAME(DW, DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @startDate)) AS Day
    FROM sys.all_objects a CROSS JOIN sys.all_objects b;

score 0 · Answer 5 · answered Apr 12 '16 at 14:04

DECLARE @dayStart int = 13, --The day of current month
        @dayEnd int = 12,   --The day of another month
        @howManyMonth int = 1, --How many month to take
        @dateStart date,
        @dateEnd date
--Here we determine range of the dates
SELECT @dateStart = CONVERT (date,
        CAST(DATEPART(Year,GETDATE()) as nvarchar(5))+ '-' + 
        CASE WHEN LEN(CAST(DATEPART(Month,GETDATE()) as nvarchar(5))) = 1 
             THEN '0'+CAST(DATEPART(Month,GETDATE()) as nvarchar(5)) 
             ELSE CAST(DATEPART(Month,GETDATE()) as nvarchar(5)) END + '-' +
        CAST (@dayStart as nvarchar(5))),
        @dateEnd = CONVERT (date,
        CAST(DATEPART(Year,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5))+ '-' + 
        CASE WHEN LEN(CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5))) = 1 
             THEN '0'+CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5)) 
             ELSE CAST(DATEPART(Month,DATEADD(Month,@howManyMonth,GETDATE())) as nvarchar(5)) END + '-' +
        CAST (@dayEnd as nvarchar(5)))

;WITH cte AS (
SELECT @dateStart as date_
UNION ALL
SELECT DATEADD(day,1,date_) 
FROM cte
WHERE DATEADD(day,1,date_) <= @dateEnd
)
--Get results
SELECT  DATENAME(WEEKDAY,date_) as [DayOfWeek], 
        COUNT(*) as [DaysCount]
FROM cte
GROUP BY    DATEPART(WEEKDAY,date_),
            DATENAME(WEEKDAY,date_)
ORDER BY DATEPART(WEEKDAY,date_)
OPTION (MAXRECURSION 0)

Output:

DayOfWeek   DaysCount
----------- -----------
Sunday      4
Monday      4
Tuesday     4
Wednesday   5
Thursday    5
Friday      4
Saturday    4

(7 row(s) affected

SQL Query for getting nth Weekday date between two dates

5 Answers5