current_date casting

Question

string selectSql = "update " + table + " set state_" + mode + "_id=1 WHERE stoping_" + mode + " < current_date;";

When I call current_date, it returns yyyy-MM-dd format, but I want to return dd.MM.yyyy format, how can I do that. please help. My program works fine when I am trying

string selectSql = "update " + table + " set state_" + mode + "_id=1 WHERE stoping_" + mode + " < '16.04.2010';";

Answer 1

The MySQL date format is YYYY-MM-DD, but using str_to_date() and date_format() you can change the date format.

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html

In your case, try DATE_FORMAT(current_date, '%d.%M.%Y')

Answer 2

mysql has a function that return current date - curdate()

So, your query must be like

update table set state_mode_id=1 WHERE stoping_mode < curdate()

But you have 2 major faults (or even 3):

1. date field must have a format of YYYY-MM-DD, not anything else
2. such an update is senseless at all, because all such states being evaluated at select time
3. assembling table name at query time is a sign of very bad database design