CODEWITHSUNDEEP
sql-server-2008datetimefiltergetdate
Shrout1
Shrout1

Reputation: 2607

SQL Query to restrict return dates to current month

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

Upvotes: 4

Views: 77006

Answers (7)

SAMSON ADETOYI
SAMSON ADETOYI

Reputation: 1

This will truncate the startdate by month, thereby picking only the startdate with the current month:

SELECT * FROM dbo.table1
WHERE trunc(Start_Date, 'MM') = trunc(sysdate, 'MM')
ORDER BY User, Start_Date

Upvotes: 0

Leinad Moyo
Leinad Moyo

Reputation: 1

Try this for MySQL

select date(date_joined),count(*) from users where MONTH(date_joined)=MONTH(now()) and YEAR(date_joined)=YEAR(now()) group by date(date_joined);

Upvotes: -1

parseinteger
parseinteger

Reputation: 111

Just consolidate your WHERE statement into something like:

DATEDIFF(m, DATEFIELD, GETDATE()) = 0

Upvotes: 11

HABO
HABO

Reputation: 15816

The following query can take advantage of indexes since it does not perform calculations on every row. In addition, it returns as "active" any project that is active at any time during the month, e.g. a project that starts in the last week of the month and ends several months hence. And it's easy to test and modify since it separates the date arithmetic from the query.

declare @Today as Date = GetDate()
declare @StartOfMonth as Date = DateAdd( day, 1 - Day( @Today ), @Today )
declare @EndOfMonth as Date = DateAdd( day, -1, DateAdd( month, 1, @StartOfMonth ) )

select @Today as [Today], @StartOfMonth as [StartOfMonth], @EndOfMonth as [EndOfMonth]

select *
  from Table1
  where Start_Date <= @EndOfMonth and End_Date >= @StartOfMonth

Upvotes: 3

Steve
Steve

Reputation: 73

In MySql, I use something similar to update data within x number of days, perhaps something like: start_date <= DATE_SUB(curdate(), INTERVAL 10 DAY); Just change 10 to whatever number of days you need.

Upvotes: 0

Julien Blanchard
Julien Blanchard

Reputation: 825

You should try using convert(date, GetDate())GetDate().

Dates are represented as ticks, meaning smaller than seconds. if you compare GetDate() (which is 2013-05-31 11:09:45:1024 for exmaple, unsure about millisecond precision in mssql), it will always be greater than 2013-05-31, because of the hours/minutes/seconds. Your 2 choices are to compare YOURDATE >= Start_Date AND End_Date + 1 > YOURDATE, or YOURDATE >= Start_Date AND End_Date >= convert(date, YOURDATE)

First options asks for it to be stricly less than the morrow of the end date (so basically the very end of that day), second one asks for it to be within the same day as the end date, ignoring ticks. Both yields the exact same result, one of them is probably more performant, however I can't help on that side of things.

EDIT : or you can use the other two's answer, except that it will only work if you always use monthly periods. and in that case it would be simpler to redesign your database to only have a 1-12 field representing the month.

Upvotes: 0

suff trek
suff trek

Reputation: 39767

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

Upvotes: 13

Related Questions