CODEWITHSUNDEEP
sql-serversql-server-2008t-sql
oshirowanen
oshirowanen

Reputation: 15925

Check if a date range has a weekend

If I have 2 dates, I know I can work out how many days, hours, minutes etc are between the 2 dates using datediff, e.g:

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-15';

select datediff( hour, @start, @end );

How do I figure out if the date range includes a weekend?

The reason why I want to know if the date range includes a weekend is because I want to subtract the weekend from the day or hour count. i.e. if the start day is Friday, and the end date is Monday, I should only get 1 days or 24 hours.

Datepart 1 = Sunday, and datepart 7 = Saturday on my server.

Upvotes: 12

Views: 17706

Answers (7)

justin4480
justin4480

Reputation: 1431

Similar problem, although in my case I wanted a onehot encoded binary list of columns (Mon-Sun) of whether two dates contain that DOW using Impala SQL.

select ...

max(case when tripduration > 6 then 1
         when 2 >= dayofweek(tripstartdate) and 2 <= dayofweek(tripstartdate) + tripduration then 1
         when 2 <= dayofweek(tripenddate)   and 2 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '2 mon',

max(case when tripduration > 6 then 1
         when 3 >= dayofweek(tripstartdate) and 3 <= dayofweek(tripstartdate) + tripduration then 1
         when 3 <= dayofweek(tripenddate)   and 3 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '3 tue',

max(case when tripduration > 6 then 1
         when 4 >= dayofweek(tripstartdate) and 4 <= dayofweek(tripstartdate) + tripduration then 1
         when 4 <= dayofweek(tripenddate)   and 4 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '4 wed',

max(case when tripduration > 6 then 1
         when 5 >= dayofweek(tripstartdate) and 5 <= dayofweek(tripstartdate) + tripduration then 1
         when 5 <= dayofweek(tripenddate)   and 5 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '5 thu',

max(case when tripduration > 6 then 1
         when 6 >= dayofweek(tripstartdate) and 6 <= dayofweek(tripstartdate) + tripduration then 1
         when 6 <= dayofweek(tripenddate)   and 6 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '6 fri',

max(case when tripduration > 6 then 1
         when 7 >= dayofweek(tripstartdate) and 7 <= dayofweek(tripstartdate) + tripduration then 1
         when 7 <= dayofweek(tripenddate)   and 7 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '7 sat',

max(case when tripduration > 6 then 1
         when 1 >= dayofweek(tripstartdate) and 1 <= dayofweek(tripstartdate) + tripduration then 1
         when 1 <= dayofweek(tripenddate)   and 1 >= dayofweek(tripenddate) - tripduration   then 1 else 0 end) as '1 sun'

from ....

Upvotes: 0

Talasila
Talasila

Reputation: 161

You can use a recursive CTE to get the dates between the range

     WITH    CTE_DatesTable
          AS ( SELECT   @MinDate AS [EffectiveDate]
               UNION ALL
               SELECT   DATEADD(dd, 1, [EffectiveDate])
               FROM     CTE_DatesTable
               WHERE    DATEADD(dd, 1, [EffectiveDate]) <= @MaxDate )

            SELECT  [EffectiveDate]
            FROM    CTE_DatesTable
    OPTION  ( MAXRECURSION 0 );

and then Filter out the Weekends using ..

((DATEPART(dw, DT.EffectiveDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

Upvotes: 0

Hiren Dhaduk
Hiren Dhaduk

Reputation: 2780

Here is the simple and generalize query . you can achieve result through recursive query . Check following query 

with mycte as
(
  select cast('2013-06-14' as datetime) DateValue
  union all
  select DateValue + 1 from mycte where DateValue + 1 < '2013-06-17'
)

select count(*) as days , count(*)*24 as hours
from    mycte
WHERE DATENAME(weekday ,DateValue) != 'SATURDAY' AND 
DATENAME(weekday ,DateValue) != 'SUNDAY'
OPTION (MAXRECURSION 0)

it will definitely work for you .

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460058

You can use following functions. The first moves a given start- or enddate to monday(friday if backwards) if it begins in the weekend. The second calculates the seconds between two dates without weekends. Then you just need to check if the total-days equals the days without weksends(demo below).

CREATE FUNCTION [dbo].[__CorrectDate](
    @date DATETIME,
    @forward INT
)

RETURNS DATETIME AS BEGIN
    IF (DATEPART(dw, @date) > 5) BEGIN

        IF (@forward = 1) BEGIN
            SET @date = @date + (8 - DATEPART(dw, @date))
            SET @date = DateAdd(Hour, (8 - DatePart(Hour, @date)), @date)
        END ELSE BEGIN
            SET @date = @date - (DATEPART(dw, @date)- 5)
            SET @date = DateAdd(Hour, (18 - DatePart(Hour, @date)), @date)
        END
        SET @date = DateAdd(Minute, -DatePart(Minute, @date), @date)
        SET @date = DateAdd(Second, -DatePart(Second, @date), @date)
    END

    RETURN @date
END

GO

CREATE FUNCTION [dbo].[__DateDiff_NoWeekends](
    @date1 DATETIME,
    @date2 DATETIME
)

RETURNS INT AS BEGIN
    DECLARE @retValue INT

    SET @date1 = dbo.__CorrectDate(@date1, 1)
    SET @date2 = dbo.__CorrectDate(@date2, 0)

    IF (@date1 >= @date2)
        SET @retValue = 0
    ELSE BEGIN
        DECLARE @days INT, @weekday INT
        SET @days = DATEDIFF(d, @date1, @date2)
        SET @weekday = DATEPART(dw, @date1) - 1

        SET @retValue = DATEDIFF(s, @date1, @date2) - 2 * 24 * 3600 * ((@days + @weekday) / 7) 
    END

    RETURN @retValue
END

Then you can get the info in this way:

declare @start datetime
set @start = '20130614'

declare @end datetime
set @end = '20130615'

declare @daysTotal int
declare @daysWoWeekends int

SET @daysTotal = DATEDIFF(dd, @start, @end)
SET @daysWoWeekends = dbo.__DateDiff_NoWeekends(@start, @end) / (24 * 3600)

SELECT CASE WHEN @daysTotal = @daysWoWeekends
       THEN 'No weekend between'
       ELSE 'There are weeksends' END,
       @daysTotal,
       @daysWoWeekends,@start,@end

Here's a demo: http://sqlfiddle.com/#!6/7cda7/11

There are weeksends 1   0   June, 14 2013 00:00:00+0000 June, 15 2013 00:00:00+0000

Upvotes: 0

JamieA
JamieA

Reputation: 2013

I have a function that calculates working days between 2 dates, the basic query is 

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-17';
SELECT 
   (DATEDIFF(dd, @Start, @end) +1)  -- total number of days (inclusive)
  -(DATEDIFF(wk, @Start, @end) * 2) -- number of complete weekends in period
  -- remove partial weekend days, ie if starts on sunday or ends on saturday
  -(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END) 
  -(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END)

so you could work out if dates include weekend if working days different to datediff in days

  SELECT case when  (DATEDIFF(dd, @Start, @end) +1) <>
   (DATEDIFF(dd, @Start, @end) +1)  -- total number of days (inclusive)
  -(DATEDIFF(wk, @Start, @end) * 2) -- number of complete weekends in period
  -- remove partial weekend days, ie if starts on sunday or ends on saturday
  -(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END) 
  -(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END) then 'Yes' else 'No' end as IncludesWeekends

or simpler

SELECT   (DATEDIFF(wk, @Start, @end) * 2) +(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)      +(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END)  as weekendDays

Upvotes: 8

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

You have a weekend day if any one of the following three conditions is true:

  1. The day of week (as an integer) of the end date is less than the day of week of the start date

  2. Either day is itself a weekend day

  3. The range includes at least six days

.

select 
    Coalesce(
    --rule 1
    case when datepart(dw,@end) - datepart(dw,@start) < 0 then 'Weekend' else null end,
    -- rule 2
    -- depends on server rules for when the week starts
    -- I think this code uses sql server defaults
    case when datepart(dw,@end) in (1,7) or datepart(dw,@start) in (1,7) then 'Weekend' else null end,
    --rule 3
    -- six days is long enough
    case when datediff(d, @start, @end) >= 6 then 'Weekend' Else null end,
    -- default
    'Weekday')

Upvotes: 3

SQLMenace
SQLMenace

Reputation: 134941

One way, just showing you how you can use a table of numbers for this

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-15'; -- play around by making this 2013-06-14 and other dates


IF EXISTS (SELECT * FROM(
SELECT DATEADD(dd,number,@start) AS SomeDAte 
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd,number,@start) BETWEEN @start AND @end) x
WHERE DATEPART(dw,SomeDate) IN(1,7))  -- US assumed here
SELECT 'Yes'
ELSE
SELECT 'No'

Example to return all weekends between two dates

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-30';



SELECT DATEADD(dd,number,@start) AS SomeDAte 
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd,number,@start) BETWEEN @start AND @end
AND DATEPART(dw,DATEADD(dd,number,@start)) IN(1,7)

Results

2013-06-15 00:00:00.000
2013-06-16 00:00:00.000
2013-06-22 00:00:00.000
2013-06-23 00:00:00.000
2013-06-29 00:00:00.000
2013-06-30 00:00:00.000

Upvotes: 1

Related Questions