Cjuidatepicker / Mysql / Regional Date Formatting.


My CJuiDatePicker has a date fomatting of dd/mm/yyyy which is the UK’s normal setting.

‘visit_adviser_notified_date’ = 01/12/2012

I regretted allowing the datepicker to save dates in this format, and not as a MySQL Date/Timestamp 2010-12-04 00:00:00

Now we are at the point of needing to do date and time calculations, and the dates are in our national format as above (dd/mm/yyy)…

When the dates are in MySQL format (2010-12-04 00:00:00), all is well, I can do the following to find the interval between two dates:

DATEDIFF(CURRENT_TIMESTAMP, `visit_adviser_notified_date`) AS days


But, as the date is in dd/mm/yyyy format, im trying the following, but cant get it to work, as DATEDIFF doesnt understand our UK format i guess?

DATEDIFF(DATE_FORMAT(NOW(), '%d/%m/%Y'), `visit_adviser_notified_date`) AS days

Any thoughts?

Also, how can I display a date as ‘04/12/2010’ in the form, when it’s saved in the DB as ‘2010-12-04 00:00:00’??

Any thoughts?


ok a simple minus one from the other works. k.i.s.s.

(DATE_FORMAT(NOW(), '%d/%m/%Y')) - (`visit_adviser_notified_date`) AS bingo

interesting value if visit_adviser_notified_date is empty though…