date = curdate

[mrhankey]

hi i have a query which should work however it returns nothing.

i have at least 2 entries where completed = No and the date of the task is today.

the code is:

SELECT count(TaskID) AS DueToday, DATE_FORMAT(`TaskDueDate`,'%d.%m.%Y') AS XLABEL FROM tasks
WHERE 'XLABEL' = CURDATE( ) AND Completed = 'No'

however it returns 0

TaskDueDate column in the database is a datetime type that is why i am using dateformat to only display the date of the column as opposed to the date and time.

thanks again

[mrhankey]

i have even tried changing it to this:

SELECT count(TaskID) AS DueToday, DATE_FORMAT(`TaskDueDate`,'%Y-%m-%d') AS XLABEL FROM tasks
WHERE 'XLABEL' = CURDATE( ) AND Completed = 'No'

i think it is the curdate( ) that is causing the issue however i have tried NOW() and still does not work.

[simplypixie]

Try changing

CURDATE()

to

date('Y-m-d')

[mrhankey]

no still does not return and count of tasks due today.

anything else i can try?

[simplypixie]

Just realised your column to compare with today's date is formatted incorrectly, should be:

SELECT count(TaskID) AS DueToday, DATE_FORMAT(`TaskDueDate`,'%Y-%m-%d') AS XLABEL FROM tasks
WHERE `XLABEL` = date('Y-m-d') AND Completed = 'No'

You had ' ' around the XLABEL which is incorrect, is using quotes around column names they should be like ` `. Or you can just get rid of them altogether to prevent this issue in future (for instance you haven't put them around your Completed column name).

Also check that your column names are in the correct case as they are case sensitive - so is your XLABEL column actually all in caps in your database?

[mrhankey]

thanks for this, XLABEL is not a column in the database, TaskDueDate is the column i am using XLABEL as an alias as i need to trim TaskDueDate column from a datetime type to just the date.

i ran your query however no joy.

thanks again

[simplypixie]

Oh yes, I had missed that - you can't use an alias like you are. Just change the where clause to use your correct column

WHERE TaskDueDate = date('Y-m-d')

[mrhankey]

yeah thanks for that, only issue is it returns 0 back where there is definately an entry.

really strange.

[mrhankey]

strange thing is the overdue query i have works fine

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

i did try changing it to

tasks.TaskDueDate = CURRENT_DATE()

but no joy

[simplypixie]

Have you tried running the query in PHPMyAdmin (obviously putting in some data in place of the variables) and see what happens? It should give you an error message if it is your query that isn't working.

[mrhankey]

yeah i am running it on php my admin to test the queries as always prefer to make sure they work there first.

returns no error just duetoday 0

which is definately not right.

[mrhankey]

resolved it now with thanks to a friend:

SELECT count(`TaskID`) AS `DueToday` FROM tasks WHERE DATE_FORMAT(`TaskDueDate`,'%Y-%m-%d') = DATE_FORMAT( NOW(),'%Y-%m-%d') AND `Completed` = 'No'

thanks for your help simplypixie

[simplypixie]

I was just going to say - I have run and tweaked your query and got it to work. It didn't like the as FeeID and as XLABEL so I changed those to all lower case and got the result as expected (using CURDATE()).

So the code (which is a bit more simple than what you have finished with) is:

SELECT count(TaskID) as taskid, date_format(TaskDueDate, '%d.%m.%Y') as xlabel FROM tasks WHERE TaskDueDate = CURDATE() AND Completed = 'No'

[mrhankey]

thanks simplypixie