CODEWITHSUNDEEP
sqlsql-servert-sqlsql-date-functions
user3115933
user3115933

Reputation: 4443

How to do I exclude a specific date range within a specific period?

I am using SQL Server 2012 and I need my T-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 T-SQL query is as follows:

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

I am stuck at how to properly write the logic at Line 2!

Upvotes: 3

Views: 6651

Answers (3)

Gaurang Dave
Gaurang Dave

Reputation: 4046

This will work for you

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

Reference

Upvotes: 5

Nishant Gupta
Nishant Gupta

Reputation: 3656

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')

Upvotes: 2

EzLo
EzLo

Reputation: 14189

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'

Upvotes: 2

Related Questions