Where duedate = currentdate

[mrhankey]

Hi,

I have been trying to execute a query and for some reason it returns 0 when there should be 1 in the result.

SELECT count(TaskID) AS DueToday
FROM tasks
WHERE TaskDueDate = CURRENT_DATE AND Completed = 'No'

I have tried NOW() and various others reference http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_current-date

however it will not return anything other than 0. the TaskDueDate is a DateTime.

Should i use something other than CURRENT_DATE in the query?

Thanks

[smantscheff]

Make sure that TaskDueDate contains exactly the date value you want. Or, better, alter it to a date field instead of a datetime field.

[mrhankey]

Thanks, it needs to be a datetime is a pre populates a calendar in another part of the system which works.

when you display the calendar or view the tasks you can see there is one if for today for example at 09.26 however when i do the above query it wont return a value.

i thought it maybe something to do with using format the datetime column in the query however when i do this for the overdue tasks i get a value:

SELECT COUNT(tasks.TaskID) AS TasksDue, tasks.TaskDueDate, tasks.Completed FROM tasks WHERE tasks.TaskDueDate < CURRENT_DATE() and tasks.Completed = 'No'

i tried changing the < to = and it returns 0.

lol driving me mental.

thanks

[smantscheff]

Please show a data sample and the CREATE TABLE statement.

[d5e5]

The following works for me

CREATE TABLE tasks(
TaskID INT AUTO_INCREMENT ,
TaskDueDate DATETIME,
Completed TEXT( 3 ) ,
PRIMARY KEY ( TaskID )
)

INSERT INTO `tasks` (
`TaskID` ,
`TaskDueDate` ,
`Completed`
)
VALUES (
NULL , NOW( ) , 'No'
);

SELECT count( TaskID ) AS DueToday
FROM tasks
WHERE DATE( TaskDueDate ) = CURRENT_DATE
AND Completed = 'No';

+----------+
| DueToday |
+----------+
|        1 | 
+----------+
1 row in set (0.00 sec)