CODEWITHSUNDEEP
sqlsql-server-2008t-sql
Ankit Tyagi
Ankit Tyagi

Reputation: 175

Weekend dates between two dates in SQL

I need to find the dates of weekends between two given dates. Is there any way to find the same. i am working on SQL Server 2008.

Upvotes: 1

Views: 5154

Answers (3)

John Cappelletti
John Cappelletti

Reputation: 81960

A calendar/tally table would do the trick as well, but here we can use an ad-hoc tally table.

Declare @Date1 date = '2017-01-01'
Declare @Date2 date = '2017-03-31'

Select *
 From  (
        Select Top (DateDiff(day,@Date1,@Date2)+1) D=DateAdd(day,-1+Row_Number() Over (Order By (select null)),@Date1) 
         From  master..spt_values n1,master..spt_values n2
       ) A
 Where DateName(WEEKDAY,D) in ('Saturday','Sunday')

Returns

D
2017-01-01
2017-01-07
2017-01-08
2017-01-14
2017-01-15
2017-01-21
2017-01-22
2017-01-28
2017-01-29
2017-02-04
2017-02-05
2017-02-11
2017-02-12
2017-02-18
2017-02-19
2017-02-25
2017-02-26
2017-03-04
2017-03-05
2017-03-11
2017-03-12
2017-03-18
2017-03-19
2017-03-25
2017-03-26

Upvotes: 4

MileP
MileP

Reputation: 101

I think that this script will answer your question:

Declare @fromD datetime = '2014-09-01'
Declare @toD datetime = '2014-09-30'
Declare @cnt int = 0
DECLARE @table table (id int identity (1, 1), dt date, dtname varchar (100))
 
while @fromD < = @toD
Begin
if datename (weekday, @fromD) in ('Saturday', 'Sunday')
INSERT INTO @table
    VALUES (@fromD, DATENAME(weekday, @fromD))
 
SET @fromD = DATEADD(D, 1, @fromD)
END
SELECT
    dt,
    dtname
FROM @table

Upvotes: 1

Anurag Dadheech
Anurag Dadheech

Reputation: 629

DECLARE @fromD date = '2017-04-01', @toD date = '2017-04-30';

WITH cteDate as 
  (
   SELECT ROW_NUMBER() OVER(ORDER BY NEWID()) as id, @fromD as dt, 
   DATENAME(dw, @fromD) as dtName

   UNION ALL

   SELECT id + 1 as id, DATEADD(day, 1, dt), DATENAME(dw, DATEADD(day, 1, 
   dt)) dtName
   FROM cteDate
   WHERE dt < @toD
  )
select dt, dtName from cteDate
WHERE dtName in( 'Saturday', 'Sunday')
OPTION(MAXRECURSION 0)

returns 

dt          dtName
-------------------
2017-04-01  Saturday
2017-04-02  Sunday
2017-04-08  Saturday
2017-04-09  Sunday
2017-04-15  Saturday
2017-04-16  Sunday
2017-04-22  Saturday
2017-04-23  Sunday
2017-04-29  Saturday
2017-04-30  Sunday

Upvotes: 3

Related Questions