CODEWITHSUNDEEP
sqlsql-servert-sqldate
James Privett
James Privett

Reputation: 1109

SQL Server : get records where dates are in specific time fram

Let's say I have the folling table

EventName    |   eventStartDate  |   eventEndDate
--------------------------------------------------
Event 1      |  11/11/2015       |   01/31/2016
Event 2      |  01/24/2016       |   01/26/2016
Event 3      |  02/23/2015       |   03/20/2016
Event 4      |  02/20/2016       |   02/26/2016

I'd like to write query that gets all the events where the event takes place within a calendar month. So for example I want to get all events that are active in January. This would leave me with Event 1, Event 2 and Event 3

Any help is appreciated

Upvotes: 2

Views: 63

Answers (3)

Andrey Korneyev
Andrey Korneyev

Reputation: 26896

This can be done using a bunch of conditions like:

select * 
from your_table
where
     (eventStartDate >= '20160101' and eventStartDate <= '20160131')
     or
     (eventEndDate >= '20160101' and eventEndDate <= '20160131')
     or
     (eventStartDate <= '20160101' and eventEndDate >= '20160131')

First one defines period that starts in Jan.

Second one defines perion that ends in Jan.

Third one defines period that starts before and ends after Jan (inclusively).

Obviously - there are no other periods intersecting this particular January possible.

Update:

All these conditions could be simplified to:

select * 
from your_table
where eventStartDate <= '20160131' and eventEndDate >= '20160101'

This condition still defines all three periods mentioned above, but significantly shorter.

Upvotes: 3

vikas singh aswal
vikas singh aswal

Reputation: 202

DECLARE 
@CurrentMonth int =1 -- MONTH IN NUMBERS

Please this 

SELECT *
    EventName    
    ,eventStartDate  
    ,eventEndDate
FROM EVENTs
WHERE
YEAR(eventStartDate) =YEAR(GETUTCDATE())
AND (MONTH(eventStartDate)>= @CurrentMonth AND MONTH(eventStartDate)<(@CurrentMonth+1))

Upvotes: 0

Onkel Toob
Onkel Toob

Reputation: 2212

A different approach would be to compute a six-digit number for each month and then use the BETWEENoperator:

SELECT
     [EventName]
     , [eventStartDate]
     , [eventEndDate]
FROM [tblEvents]
WHERE 201601 BETWEEN YEAR([eventStartDate]) * 100 + MONTH([eventStartDate]) AND YEAR([eventEndDate]) * 100 + MONTH([eventEndDate])

Update: With huge data sets, however, there will be a lot of computing going on, so performance has to be kept in mind.

Upvotes: 1

Related Questions