Mysql How do you define a date range for the current month

Hi
I am wanting to write a mysql query that only looks at the current month.
How would you write this where clause

My query:

SELECT *
FROM Task_assigned
Where Completed = 0 and DateDue between (this is where I do not know how to assign the range for the current month)
matthewdacruzAsked:
Who is Participating?
 
mankowitzCommented:
I am not sure what you mean by dynamically. Are you calling this query from a scripting language? if so, your script could do it. Alternatively, if you want it to just print the current month, you could do this:

SELECT *
FROM Task_assigned
Where Completed = 0 and MONTH(DateDue ) = month(NOW()) and YEAR(DateDue)=YEAR(NOW())
0
 
mankowitzCommented:
if all you want is the month, then use the MYSQL month function. Seel below for january

SELECT *
FROM Task_assigned
Where Completed = 0 and MONTH(DateDue ) = 1


if all you want a specific month, you have to include the year as well.

SELECT *
FROM Task_assigned
Where Completed = 0 and MONTH(DateDue ) = 1 and YEAR(DateDue)=2011
0
 
matthewdacruzAuthor Commented:
Hi Thank you for your response.

Is there a way to dynamically define the month and year as the report needs to automatically grab each months tasks without having to  hard code it in?
0
 
Patrick MatthewsCommented:
In SQL Server you could do this with DATEDIFF/DATEADD; I presume something similar can be done in MySQL:

SELECT *
FROM Task_assigned
Where Completed = 0 and 
    DateDue >= DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()), '2000-01-01') AND
    DateDue < DATEADD(month, DATEDIFF(month, '2000-01-01', GETDATE()) + 1, '2000-01-01')

Open in new window

0
 
matthewdacruzAuthor Commented:
Thanks that worked perfect.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.