T-SQL to Display Weekends Between two Dates

Different ways to find the weekend between two given dates. The SQL requires @begindate and @endate paramteters to be entered in the below SQL

Download T-SQL WeekendBetweenTwoValidDates

DECLARE @beginDate Date='20150101',
		@endDate Date='20150131'


DECLARE @Calendar Table
(CalendarDate Date Primary key, IsWeekend Bit)

WHILE @beginDate <= @endDate
BEGIN
INSERT INTO @Calendar
SELECT
@beginDate As CalendarDate
,(Case When DATEPART(Weekday, @beginDate) In (7, 1) Then 1 Else 0 End) As IsWeekend
Set @beginDate = DateAdd(Day, 1, @beginDate)
End

SELECT CalendarDate From @Calendar Where IsWeekend = 1

On SQL 2012 or higher version you can use choose function.

http://msdn.microsoft.com/en-us/library/hh213019.aspx

DECLARE @beginDate Date='20150101',
 @EndDate Date='20150131'


Declare @Calendar Table
(CalendarDate Date Primary key, IsWeekend varchar(20))

While @beginDate <= @endDate
Begin
Insert Into @Calendar
SELECT 
@beginDate As CalendarDate,
CHOOSE(DATEPART(dw, @beginDate), 'WEEKEND','Weekday',
'Weekday','Weekday','Weekday','Weekday','WEEKEND') 
Set @beginDate = DateAdd(Day, 1, @beginDate) 
End

Select CalendarDate From @Calendar where IsWeekend='WEEKEND'

Using Number table

Declare @beginDate date = '20150101', @endDate date = '20150131'

SELECT 
DATEADD(dd, Number, @beginDate)
from
(
SELECT
 (a.Number * 256) + b.Number AS Number
FROM 
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) a (Number),
 (
 SELECT number
 FROM master..spt_values
 WHERE 
 type = 'P'
 AND number <= 255
 ) b (Number)
)T
Where
Number >= 0
and DATEADD(dd, Number, @beginDate) <= @endDate
and DATEPART(dw, DATEADD(dd, Number, @beginDate)) in ( 1, 7 )

Ouput:-

Weekend_Between_Two_Dates

Advertisements

About Prashanth Jayaram

DB Technologist, Author, Blogger, Operations-Mgr at CTS, Automation Expert, Technet WIKI Ninja, MVB and Powershell Geek You can connect me via https://social.technet.microsoft.com/profile/prashanth jayaram/ http://www.sqlshack.com/author/prashanth/ https://www.tumblr.com/blog/prashantjayaram http://www.sqlservercentral.com/blogs/powersql-by-prashanth-jayaram/ http://www.toadworld.com/members/prashanthjayaram/ My Articles are published in following sites http://www.ssas-info.com/analysis-services-articles/ http://db-pub.com/ http://www.sswug.org/sswugresearch/community/
This entry was posted in T-SQL and tagged , , , , , , , , . Bookmark the permalink.

2 Responses to T-SQL to Display Weekends Between two Dates

  1. Bobby Tables says:

    Hi Prashanth,

    This is pretty trivial (and set-based! No WHILE loops!) if you use a “numbers” table.
    (Why should you have one? http://sqlblog.com/blogs/adam_machanic/archive/2006/07/12/you-require-a-numbers-table.aspx)

    Assume a table “dbo”.”Numbers” with a column “N” containing integers from 0 to 1,000,000.

    Solution:

    declare
    @beginDate date = ‘2015-01-01’
    , @endDate date = ‘2015-01-31’

    select
    DATEADD(dd, N, @beginDate)
    from
    dbo.Numbers
    where
    N >= 0
    and DATEADD(dd, N, @beginDate) <= @endDate
    and DATEPART(dw, DATEADD(dd, N, @beginDate)) in ( 1, 7 )

    • Thanks for the Tip

      Modified query

      Declare
      @beginDate date = ‘20150101’
      , @endDate date = ‘20150131’

      SELECT
      Number,
      DATEADD(dd, Number, @beginDate)
      from
      (
      SELECT
      (a.Number * 256) + b.Number AS Number
      FROM
      (
      SELECT number
      FROM master..spt_values
      WHERE
      type = ‘P’
      AND number <= 255
      ) a (Number),
      (
      SELECT number
      FROM master..spt_values
      WHERE
      type = 'P'
      AND number = 0
      and DATEADD(dd, Number, @beginDate) <= @endDate
      and DATEPART(dw, DATEADD(dd, Number, @beginDate)) in ( 1, 7 )

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s