SQL Query to restrict return dates to current month

Shrout1 picture Shrout1 · May 31, 2013 · Viewed 61.3k times · Source

I am trying to restrict my returned data to only those points that have start and end dates in the current month - active projects. It is behaving problematically because today is the last day of the month. I believe that tomorrow will be a problem as well (no June data included in the sample).

Here is my data set (Table 1):

Project                      User                 Effort  Start_Date End_Date
-------                      -------              ------  --------   --------
Traffic Control              DOMAIN\john.smith    0.1     5/1/2013   5/31/2013
Turboencabulator Analysis    DOMAIN\mark.webber   0       5/1/2013   5/31/2013
Widget Calibration           DOMAIN\mark.webber   0       5/1/2013   5/31/2013
Gizmo Creation               DOMAIN\steve.green   0.1     5/1/2013   5/31/2013
Advanced Toolmaking          DOMAIN\steve.green   0.6     5/1/2013   5/31/2013
Diesel Engine Diagnostics    DOMAIN\steve.green   0.05    5/1/2013   5/31/2013
Cold Fusion Reactor Creation DOMAIN\steve.green   0.3     5/1/2013   5/31/2013

When using the following query today I get no returned results:

SELECT * FROM dbo.table1
WHERE Start_Date <= (getdate()) AND End_Date >= (getdate())
ORDER BY User, Start_Date

Yesterday it was returning just fine. I have data for June as well (not displayed in my sample) but I need to modify my statement such that it will reliably return data for the current month throughout the entirety of the month.

Answer - Correct WHERE statement (from comments in answer below):

WHERE (Month(Start_Date) <= Month((getdate())) AND Month(End_Date) >= Month((getdate()))) AND (YEAR(Start_Date) <= YEAR((getdate())) AND YEAR(End_Date) >= YEAR((getdate())))

Answer

Yuriy Galanter picture Yuriy Galanter · May 31, 2013

Use TSQL Month function:

SELECT * FROM dbo.table1
WHERE Month(Start_Date) = Month(getdate()) AND Month(End_Date) = Month(getdate())
ORDER BY User, Start_Date