Interview Question of the Week #047 – Logic To Find Weekdays Between Two Dates

I often enjoy seeing interview questions where the contestant has to write SQL Scripts. Here is another such example.

Question: How to find weekdays between two dates?

Answer: Here is a function which can be used to find weekdays between two dates.

CREATE FUNCTION dbo.getDayCount(@startdate date, @enddate date)
RETURNS INT
AS
BEGIN
DECLARE
@count INT = 0
WHILE @startdate<=@enddate
BEGIN
IF
DATEPART(dw,@startdate) > 1 AND DATEPART(dw,@startdate) < 7
BEGIN
SET
@count = @count + 1
END
SET
@startdate = DATEADD(DAY,1,@startdate)
END
RETURN
@count
END
-- Execute Function
SELECT dbo.getDayCount ('10/10/2015', '11/10/2015')

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
Previous Post
SQL SERVER 2016 – Comparing Execution Plans
Next Post
SQL SERVER – Practical Tips to Reduce SQL Server Database Table Size – Experts Opinion

Related Posts

No results found.

13 Comments. Leave new

  • That was too much work.

    SELECT DATEDIFF ( d, ’10/10/2015′ , ’11/10/2015′ );

    Reply
    • This is shorter and cleaner answer.

      Reply
      • Adrian Greenwood
        November 29, 2015 4:32 pm

        The DATEDIFF solution in the comments is the number of days between 2 dates which doesn’t answer the original question which is the number of weekdays (Mon – Fri) between the dates?

        Also, in the getDayCount function solution, wouldn’t you have to set datefirst at the beginning to ensure that your weekend does fall on days 1 and 7?

      • How come above is correct answer? Asked workdays only. Datediff() function gives no. of days only.

    • Your command counts every day between the dates. The posted function counts only working days, monday – friday without saturday and sunday.

      Reply
      • Heiko is correct. Pinals function counts ONLY weekdays. The DATEDIFF counts ALL days.

        HOWEVER In Pinals function – DATEPART(dw… depends on the following setting being 7 as his greater than assumes DATEFIRST is at its default value of 7. If you’re getting unexpected results; try checking @@DATEFIRST

        SELECT @@DATEFIRST
        SET DATEFIRST 7

    • This will display the days between the start date and end date, not provide the weekdays.

      Reply
  • — alternate way
    DECLARE @startDate DATETIME, @endDate DATETIME
    SELECT @startDate = ‘2015/10/10’, @endDate = ‘2015/11/10’ ;

    ;WITH AllDays
    AS(
    SELECT @startDate AS WorkDay, DATEDIFF(DAY, @startDate, @endDate) AS DiffDays
    UNION ALL
    SELECT DATEADD(dd, 1, WorkDay),DiffDays -1
    FROM AllDays wd
    WHERE DiffDays > 0
    )
    SELECT COUNT(*)
    FROM AllDays
    WHERE DATEPART(dw, WorkDay ) IN (2, 3, 4, 5, 6);

    Reply
  • Hello,

    I got 22 instead of 1.

    check and verify. thanks

    Reply
  • Heiko is correct. Pinals function counts ONLY weekdays. The DATEDIFF counts ALL days.

    HOWEVER In Pinals function – DATEPART(dw… depends on the following setting being 7 as his greater than assumes DATEFIRST is at its default value of 7. If you’re getting unexpected results; try checking @@DATEFIRST

    SELECT @@DATEFIRST
    SET DATEFIRST 7

    Reply
  • What’s with all the looping or even-more-expensive recursion?? Use a simple math calc instead. Also, for efficiency, get rid of all local variables and just use a single RETURN statement.

    CREATE FUNCTION dbo.GetWeekDaysCount (
    @fromDate datetime,
    @toDate datetime
    )
    –SELECT dbo.GetWeekDaysCount(‘20151010’, ‘20151110’)
    RETURNS int
    BEGIN
    RETURN (
    –declare @fromdate datetime, @todate datetime select @fromdate = ‘20151010’, @todate = ‘20151110’
    SELECT *,
    (days / 7 * 5) + (days % 7) –workdays in whole weeks (if any) plus total days in partial week (if any)
    – CASE WHEN 6 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Saturday
    – CASE WHEN 7 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Sunday
    FROM (
    SELECT
    DATEDIFF(DAY, @fromDate, @toDate) + 1 AS days, –total days between the two dates
    DATEDIFF(DAY, 0, @fromDate) % 7 + 1 AS wkdy –dayofweek: 1=Mon,…,6=Sat,7=Sun REGARDLESS OF SQL SETTINGS!
    ) AS derived
    )
    END –FUNCTION

    Reply
  • CREATE FUNCTION dbo.GetWeekDaysCount (
    @fromDate datetime,
    @toDate datetime
    )
    –SELECT dbo.GetWeekDaysCount(‘20151010’, ‘20151110’)
    RETURNS int
    BEGIN
    RETURN (
    –declare @fromdate datetime, @todate datetime select @fromdate = ‘20151010’, @todate = ‘20151110’
    SELECT *,
    (days / 7 * 5) + (days % 7) –workdays in whole weeks (if any) plus total days in partial week (if any)
    – CASE WHEN 6 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Saturday
    – CASE WHEN 7 BETWEEN wkdy AND wkdy + days % 7 – 1 THEN 1 ELSE 0 END –minus 1 if partial week includes Sunday
    FROM (
    SELECT
    DATEDIFF(DAY, @fromDate, @toDate) + 1 AS days, –total days between the two dates
    DATEDIFF(DAY, 0, @fromDate) % 7 + 1 AS wkdy –dayofweek: 1=Mon,…,6=Sat,7=Sun REGARDLESS OF SQL SETTINGS!
    ) AS derived
    )
    END –FUNCTION

    Reply
  • declare @date1 datetime2= ’10/10/2015′, @date2 datetime2=’11/10/2015′;
    with cte1(dates)
    as
    (
    select @date1 as dates
    union all
    select dateadd(dd,1,dates) as dates
    from cte1
    where dates< @date2
    )select sum(case when datepart(weekday,dates) in (6,7) then 0 else 1 end ) as cnt from cte1;

    Reply

Leave a Reply

Menu