I have created an webapp that handles appointments. It then places those appointments stored in a mysql database onto a google map.
I want to only show the appointments for 'today'. To do this in my sql query I would need to do something along the lines select * from appointments where $date = today. Currrently it just reads select * from appointments. The problem I'm having is the date is stored in one column in the following format Fri Mar 08 2013 09:00:00 GMT 0000 (GMT). The time aspect is irrelevant when plotting on the map, all I need is to extract the date and show those records associated 'todays' date.
How do I just take the date from one column when it's formatted like it is and how can I set todays date in the same format?
Can be cumbersome... but I would solve it this way (pure SQL):
select *
from appointments
where `date` >= curdate() and `date` < date_add(curdate(), interval +1 day);
I am assuming that your 'date' field is stored as date or datetime, and not as a string. If you are storing dates as strings, then you have some big problem, since you will need to 'cast' somehow the string into a valid date.
Hope this helps you
Oh dear. You should store the date as a DATE data type. It has its own internal format. When you query the table you can use the MySQL DATE_FORMAT() function to have it presented in whatever format you require.
Once you have changed the column (and converted all the existing data into the correct format, you will have no problem performing date based queries.