Subtract weekend from DAYDIFF - Possible?

  • Hi all!

    If I in a very simple example writes:

    select DATEDIFF(day,'20150104','20150110')+1

    the result is 7 for this week.

    But i only want to count monday - Friday, i.e. the result should be 5 instead.

    The days could be found via something like:

    set datefirst 7

    select DATEPART(dw,'20150109')

    where the result shold be in (1,2,3,4,5)

    But does DATEDIFF support this?

    best regards

    Edvard Korsbæk

  • Edvard

    Not directly, no. You could do some additional fancy date arithmetic to subtract Saturdays or Sundays, or you could create a Dates table, and join to that, counting only the rows that are not marked as weekends.

    John

  • Try creating and using a date table...much simpler. This script will create an awesome one.

    Full disclosure - I found this script here - I am not the author...

    /****** Object: Table [dbo].[DateDimension] Script Date: 1/5/2016 9:27:40 AM ******/

    SET ANSI_NULLS ON

    GO

    SET QUOTED_IDENTIFIER ON

    GO

    SET ANSI_PADDING ON

    GO

    CREATE TABLE [dbo].[DateDimension](

    [DateKey] [int] NOT NULL,

    [Date] [date] NOT NULL,

    [Day] [tinyint] NOT NULL,

    [DaySuffix] [char](2) NOT NULL,

    [Weekday] [tinyint] NOT NULL,

    [WeekDayName] [varchar](10) NOT NULL,

    [IsWeekend] [bit] NOT NULL,

    [IsHoliday] [bit] NOT NULL,

    [HolidayText] [varchar](64) SPARSE NULL,

    [DOWInMonth] [tinyint] NOT NULL,

    [DayOfYear] [smallint] NOT NULL,

    [WeekOfMonth] [tinyint] NOT NULL,

    [WeekOfYear] [tinyint] NOT NULL,

    [ISOWeekOfYear] [tinyint] NOT NULL,

    [Month] [tinyint] NOT NULL,

    [MonthName] [varchar](10) NOT NULL,

    [Quarter] [tinyint] NOT NULL,

    [QuarterName] [varchar](6) NOT NULL,

    [Year] [int] NOT NULL,

    [MMYYYY] [char](6) NOT NULL,

    [MonthYear] [char](7) NOT NULL,

    [FirstDayOfMonth] [date] NOT NULL,

    [LastDayOfMonth] [date] NOT NULL,

    [FirstDayOfQuarter] [date] NOT NULL,

    [LastDayOfQuarter] [date] NOT NULL,

    [FirstDayOfYear] [date] NOT NULL,

    [LastDayOfYear] [date] NOT NULL,

    [FirstDayOfNextMonth] [date] NOT NULL,

    [FirstDayOfNextYear] [date] NOT NULL,

    PRIMARY KEY CLUSTERED

    (

    [DateKey] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    CREATE TABLE #dim

    (

    [date] DATE PRIMARY KEY,

    [day] AS DATEPART(DAY, [date]),

    [month] AS DATEPART(MONTH, [date]),

    FirstOfMonth AS CONVERT(DATE, DATEADD(MONTH, DATEDIFF(MONTH, 0, [date]),

    0)),

    [MonthName] AS DATENAME(MONTH, [date]),

    [week] AS DATEPART(WEEK, [date]),

    [ISOweek] AS DATEPART(ISO_WEEK, [date]),

    [DayOfWeek] AS DATEPART(WEEKDAY, [date]),

    [quarter] AS DATEPART(QUARTER, [date]),

    [year] AS DATEPART(YEAR, [date]),

    FirstOfYear AS CONVERT(DATE, DATEADD(YEAR, DATEDIFF(YEAR, 0, [date]), 0)),

    Style112 AS CONVERT(CHAR(8), [date], 112),

    Style101 AS CONVERT(CHAR(10), [date], 101)

    );

    DECLARE@StartDate DATE = '19900101',

    @NumberOfYears INT = 50;

    SET DATEFIRST 7;

    SET DATEFORMAT MDY;

    SET LANGUAGE US_ENGLISH;

    DECLARE@CutoffDate DATE = DATEADD(YEAR, @NumberOfYears, @StartDate);

    INSERT#dim

    (

    [date]

    )

    SELECT[y].[d]

    FROM(SELECTd = DATEADD(DAY, [x].[rn] - 1, @StartDate)

    FROM

    (SELECT TOP (DATEDIFF(DAY, @StartDate, @CutoffDate))

    rn = ROW_NUMBER() OVER (ORDER BY [s1].[object_id])

    FROM

    [sys].[all_objects] AS s1

    CROSS JOIN [sys].[all_objects] AS s2

    -- on my system this would support > 5 million days

    ORDER BY [s1].[object_id]

    ) AS x

    ) AS y;

    INSERTdbo.DateDimension WITH (TABLOCKX)

    SELECTDateKey = CONVERT(INT, Style112),

    [Date] = [date],

    [Day] = CONVERT(TINYINT, [day]),

    DaySuffix = CONVERT(CHAR(2), CASEWHEN [day] / 10 = 1 THEN 'th'

    ELSE CASE RIGHT([day], 1)

    WHEN '1' THEN 'st'

    WHEN '2' THEN 'nd'

    WHEN '3' THEN 'rd'

    ELSE 'th'

    END

    END),

    [Weekday] = CONVERT(TINYINT, [DayOfWeek]),

    [WeekDayName] = CONVERT(VARCHAR(10), DATENAME(WEEKDAY, [date])),

    [IsWeekend] = CONVERT(BIT, CASEWHEN [DayOfWeek] IN (1, 7) THEN 1

    ELSE 0

    END),

    [IsHoliday] = CONVERT(BIT, 0),

    HolidayText = CONVERT(VARCHAR(64), NULL),

    [DOWInMonth] = CONVERT(TINYINT, ROW_NUMBER() OVER (PARTITION BY FirstOfMonth,

    [DayOfWeek] ORDER BY [date])),

    [DayOfYear] = CONVERT(SMALLINT, DATEPART(DAYOFYEAR, [date])),

    WeekOfMonth = CONVERT(TINYINT, DENSE_RANK() OVER (PARTITION BY [year],

    [month] ORDER BY [week])),

    WeekOfYear = CONVERT(TINYINT, [week]),

    ISOWeekOfYear = CONVERT(TINYINT, ISOweek),

    [Month] = CONVERT(TINYINT, [month]),

    [MonthName] = CONVERT(VARCHAR(10), [MonthName]),

    [Quarter] = CONVERT(TINYINT, [quarter]),

    QuarterName = CONVERT(VARCHAR(6), CASE [quarter]

    WHEN 1 THEN 'First'

    WHEN 2 THEN 'Second'

    WHEN 3 THEN 'Third'

    WHEN 4 THEN 'Fourth'

    END),

    [Year] = [year],

    MMYYYY = CONVERT(CHAR(6), LEFT(Style101, 2) + LEFT(Style112, 4)),

    MonthYear = CONVERT(CHAR(7), LEFT([MonthName], 3) + LEFT(Style112, 4)),

    FirstDayOfMonth = FirstOfMonth,

    LastDayOfMonth = MAX([date]) OVER (PARTITION BY [year], [month]),

    FirstDayOfQuarter = MIN([date]) OVER (PARTITION BY [year], [quarter]),

    LastDayOfQuarter = MAX([date]) OVER (PARTITION BY [year], [quarter]),

    FirstDayOfYear = FirstOfYear,

    LastDayOfYear = MAX([date]) OVER (PARTITION BY [year]),

    FirstDayOfNextMonth = DATEADD(MONTH, 1, FirstOfMonth),

    FirstDayOfNextYear = DATEADD(YEAR, 1, FirstOfYear)

    FROM#dim

    OPTION(MAXDOP 1);

    GO

    ;

    WITHxAS (SELECTDateKey,

    [Date],

    IsHoliday,

    HolidayText,

    FirstDayOfYear,

    DOWInMonth,

    [MonthName],

    [WeekDayName],

    [Day],

    LastDOWInMonth = ROW_NUMBER() OVER (PARTITION BY FirstDayOfMonth,

    [Weekday] ORDER BY [Date] DESC)

    FROMdbo.DateDimension

    )

    UPDATEx

    SET[x].[IsHoliday] = 1,

    [x].[HolidayText] = CASEWHEN ([x].[Date] = [x].[FirstDayOfYear])

    THEN 'New Year''s Day'

    WHEN (

    [x].[DOWInMonth] = 3

    AND [x].[MonthName] = 'January'

    AND [x].[WeekDayName] = 'Monday'

    ) THEN 'Martin Luther King Day' -- (3rd Monday in January)

    WHEN (

    [x].[DOWInMonth] = 3

    AND [x].[MonthName] = 'February'

    AND [x].[WeekDayName] = 'Monday'

    ) THEN 'President''s Day' -- (3rd Monday in February)

    WHEN (

    [x].[LastDOWInMonth] = 1

    AND [x].[MonthName] = 'May'

    AND [x].[WeekDayName] = 'Monday'

    ) THEN 'Memorial Day' -- (last Monday in May)

    WHEN (

    [x].[MonthName] = 'July'

    AND [x].[Day] = 4

    ) THEN 'Independence Day' -- (July 4th)

    WHEN (

    [x].[DOWInMonth] = 1

    AND [x].[MonthName] = 'September'

    AND [x].[WeekDayName] = 'Monday'

    ) THEN 'Labour Day' -- (first Monday in September)

    WHEN (

    [x].[DOWInMonth] = 2

    AND [x].[MonthName] = 'October'

    AND [x].[WeekDayName] = 'Monday'

    ) THEN 'Columbus Day' -- Columbus Day (second Monday in October)

    WHEN (

    [x].[MonthName] = 'November'

    AND [x].[Day] = 11

    ) THEN 'Veterans'' Day' -- Veterans' Day (November 11th)

    WHEN (

    [x].[DOWInMonth] = 4

    AND [x].[MonthName] = 'November'

    AND [x].[WeekDayName] = 'Thursday'

    ) THEN 'Thanksgiving Day' -- Thanksgiving Day (fourth Thursday in November)

    WHEN (

    [x].[MonthName] = 'December'

    AND [x].[Day] = 25

    ) THEN 'Christmas Day'

    END

    WHERE([x].[Date] = [x].[FirstDayOfYear])

    OR (

    [x].[DOWInMonth] = 3

    AND [x].[MonthName] = 'January'

    AND [x].[WeekDayName] = 'Monday'

    )

    OR (

    [x].[DOWInMonth] = 3

    AND [x].[MonthName] = 'February'

    AND [x].[WeekDayName] = 'Monday'

    )

    OR (

    [x].[LastDOWInMonth] = 1

    AND [x].[MonthName] = 'May'

    AND [x].[WeekDayName] = 'Monday'

    )

    OR (

    [x].[MonthName] = 'July'

    AND [x].[Day] = 4

    )

    OR (

    [x].[DOWInMonth] = 1

    AND [x].[MonthName] = 'September'

    AND [x].[WeekDayName] = 'Monday'

    )

    OR (

    [x].[DOWInMonth] = 2

    AND [x].[MonthName] = 'October'

    AND [x].[WeekDayName] = 'Monday'

    )

    OR (

    [x].[MonthName] = 'November'

    AND [x].[Day] = 11

    )

    OR (

    [x].[DOWInMonth] = 4

    AND [x].[MonthName] = 'November'

    AND [x].[WeekDayName] = 'Thursday'

    )

    OR (

    [x].[MonthName] = 'December'

    AND [x].[Day] = 25

    );

    GO

    UPDATEd

    SET[d].[IsHoliday] = 1,

    [d].[HolidayText] = 'Black Friday'

    FROMdbo.DateDimension AS d

    INNER JOIN (SELECTDateKey,

    [Year],

    [DayOfYear]

    FROMdbo.DateDimension

    WHEREHolidayText = 'Thanksgiving Day'

    ) AS src

    ON [d].[Year] = [src].[Year]

    AND [d].[DayOfYear] = [src].[DayOfYear] + 1;

    GO

    CREATE FUNCTION dbo.GetEasterHolidays (@year INT)

    RETURNS TABLE

    WITHSCHEMABINDING

    AS

    RETURN

    (

    WITHxAS (SELECT[Date] = CONVERT(DATE, RTRIM(@year) + '0'

    + RTRIM([d].[Month]) + RIGHT('0' + RTRIM([d].[Day]), 2))

    FROM(SELECT[m].[Month],

    [Day] = [m].[DaysToSunday] + 28 - (31

    * ([m].[Month]

    / 4))

    FROM

    (SELECT[Month] = 3 + ([dts].[DaysToSunday] + 40)

    / 44,

    [dts].[DaysToSunday]

    FROM

    (SELECTDaysToSunday = [paschal].[paschal]

    - ((@year + @year / 4

    + [paschal].[paschal] - 13)

    % 7)

    FROM

    (SELECTpaschal = [epact].[epact]

    - ([epact].[epact] / 28)

    FROM

    (SELECTepact = (24 + 19

    * (@year % 19))

    % 30

    ) AS epact

    ) AS paschal

    ) AS dts

    ) AS m

    ) AS d

    )

    SELECT[x].[Date],

    HolidayName = 'Easter Sunday'

    FROMx

    UNION ALL

    SELECTDATEADD(DAY, -2, [x].[Date]),

    'Good Friday'

    FROMx

    UNION ALL

    SELECTDATEADD(DAY, 1, [x].[Date]),

    'Easter Monday'

    FROMx

    );

    GO

    ;

    WITHxAS (SELECT[d].[Date],

    [d].[IsHoliday],

    [d].[HolidayText],

    h.HolidayName

    FROMdbo.DateDimension AS d

    CROSS APPLY dbo.GetEasterHolidays([d].[Year]) AS h

    WHERE[d].[Date] = h.[Date]

    )

    UPDATEx

    SET[x].[IsHoliday] = 1,

    [x].[HolidayText] = HolidayName;

    GO

    UPDATE dd SET [dd].[IsHoliday] = 0, [dd].[HolidayText] = null

    FROM dbo.DateDimension dd

    WHERE [IsHoliday] = 1

    AND [HolidayText] NOT IN (

    'New Year''s Day',

    'Memorial Day',

    'Independence Day',

    'Labour Day',

    'Thanksgiving Day',

    'Christmas Day'

    )

    GO

    DROP TABLE [#dim];

    GO

    Brad Feaker"Tantum religio potuit suadere malorum." - Lucretius
  • Ok, I think what you are asking is you only want the number of weekdays between dates, correct?

    DECLARE @Start DATETIME

    DECLARE @Stop DATETIME

    DECLARE @NDAYS INT -- Number of Weekdays between dates

    SET @Start = '2015/01/04' -- Start Date you wanted from your code

    SET @Stop = '2015/01/10' -- Stop Date you wanted from your code

    SELECT @NDAYS =

    (DATEDIFF(dd, @Start, @Stop) + 1)

    -(DATEDIFF(wk, @Start, @Stop) * 2)

    -(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)

    -(CASE WHEN DATENAME(dw, @Stop) = 'Saturday' THEN 1 ELSE 0 END)

  • Thanks all!

    It took me quite some time to understand the solution from 'SQLMark2012

    ', but it seems rock stable.

    Best regards

    Edvard Korsbæk

  • Yes, its awesome:-)!

    Just a little comment - Cant find Easter as holiday.

    Could be done by the way.

    Best regards

    Edvard Korsbæk

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply