How do I exclude Weekend days in a SQL Server query ?

How do I exclude Weekend days in a SQL Server query ?

Asked on December 26, 2018 in SQL Server.
Add Comment


  • 5 Answer(s)

    It is mandatory to take account of the current DATEFIRST settings when the calculation of day-of-week . This code can be able to exclude weekend days, using the function called @@DATEFIRST to account for the first day of the week for any possible setting .

    SELECT *
    FROM your_table
    WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)
    
    Answered on December 26, 2018.
    Add Comment

    Try to use this code,

    SELECT date_created
    FROM your_table
    WHERE DATENAME(dw, date_created) NOT IN ('Saturday', 'Sunday')
    
    Answered on December 26, 2018.
    Add Comment

    Have a look at the code, here we use the Datename() function.

    select [date_created]
    from table
    where DATENAME(WEEKDAY, [date_created]) <> 'Saturday'
    and DATENAME(WEEKDAY, [date_created]) <> 'Sunday'
    
    Answered on December 26, 2018.
    Add Comment

    When dealing with day-of-week calculations, it’s important to take account of the current DATEFIRSTsettings. This query will always correctly exclude weekend days, using @@DATEFIRST to account for any possible setting for the first day of the week.

    SELECT *
    FROM your_table
    WHERE ((DATEPART(dw, date_created) + @@DATEFIRST) % 7) NOT IN (0, 1)
    Answered on January 13, 2019.
    Add Comment
    SELECT date_created
    FROM your_table
    WHERE DATENAME(dw, date_created) NOT IN ('Saturday', 'Sunday')
    Answered on January 13, 2019.
    Add Comment


  • Your Answer

    By posting your answer, you agree to the privacy policy and terms of service.