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