CODEWITHSUNDEEP
sqlt-sqlsql-server-2012
Crescent
Crescent

Reputation: 93

Checking if any date between date range was within a specific year

I am racking my brain on this one. I have a list of campaigns and their associated start and end dates. I'm trying to write a query to list the campaigns where at least one day between the start and end date was in 2017. My original thought was just use datepart():

datepart(yyyy,StartDt = 2017) OR datepart(yyyy,EndDt = 2017)

But that wont pick up a campaign that started before 2017 and ended after 2017, like start date 1/1/2016 and end date 1/1/2018.

Upvotes: 0

Views: 64

Answers (1)

Becuzz
Becuzz

Reputation: 6866

Probably the easiest way to do this is like this (it's something I use when having to do temporal table queries):

WHERE CampaignStartDate < '2018-01-01'
AND CampaignEndDate >= '2017-01-01'

This guarantees that your campaign start and end dates had at least some overlap with the 2017 year. It's a good way of testing if two time ranges overlap at all.

Upvotes: 5

Related Questions