How to exclude a specific date range Within a Specific Period In Sql Server

I am using SQL Server 2016 and I need my SQL query to exclude a specific time range within a specific period.

My filtering logic stands as follows:

Extract all data which falls between 2016-07-01 and 2017-03-25 AND between 2017-07-01 and 2018-03-25 and also excluding all dates which are Saturdays and Sundays within the filtering period mentioned above.

My query is as follows:

SELECT * from Table1
 WHERE [CreatedOn] between ...
 AND DATENAME(dw,[CreatedOn]) NOT IN ('Saturday', 'Sunday')

how to properly write the logic here..

Answer:

SELECT * from Table1
WHERE
(
    [CreatedOn] BETWEEN '2016-07-01' AND '2017-03-25' OR
    [CreatedOn] BETWEEN '2017-07-01' AND '2018-03-25'
)
AND ((DATEPART(dw, [CreatedOn]) + @@DATEFIRST) % 7) NOT IN (0, 1)
FOR KNOWLEDGE

The master database’s syslanguages table has a DateFirst column that defines the first day of the week for a particular language. SQL Server with US English as default language, SQL Server sets DATEFIRST to 7 (Sunday) by default. We can reset any day as first day of the week using

SET DATEFIRST 5
This will set Friday as first day of week.

@@DATEFIRST returns the current value, for the session, of SET DATEFIRST.

SELECT @@DATEFIRST

Combine BETWEEN with AND and OR:

SELECT
    *
from
    Table1
WHERE
    (
        [CreatedOn] BETWEEN '2016-07-01' AND '2017-03-25' OR
        [CreatedOn] BETWEEN '2017-07-01' AND '2018-03-25'
    )
    AND DATENAME(dw,[CreatedOn]) NOT IN ('Saturday', 'Sunday')
    AND CreatedOn NOT BETWEEN '2017-01-01' AND '2017-01-02'

Or

Try this one:

SELECT * from Table1
WHERE
([CreatedOn] BETWEEN '2016-07-01' AND '2017-03-25'
 OR
[CreatedOn] BETWEEN '2017-07-01' AND '2018-03-25')
AND DATENAME(dw,[CreatedOn]) NOT IN ('Saturday', 'Sunday').