current_date casting

Question

Hi.

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

when i call current_date, it return 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

YYYY-MM-DD is mySQL's native date format. I find it hard to believe that doing a comparison against a DD-MM-YYYY string works?

Anway, what you are looking for is DATE_FORMAT().

Answer 2

Use any function like this to return your format

function ChangeDateforShow($inputdate) {
  if($inputdate>0) {
    $date  = substr($inputdate,8,2);
    $month = substr($inputdate,5,2);
    $year  = substr($inputdate,0,4);
    $show = $date.".".$month.".".$year;
    return $show;
  }
}

Answer 3

The MySQL date format is YYYY-MM-DD, but using str_to_date() and date_format() you can change 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 4

function ChangeDateforDB($inputdate) {
  if($inputdate>0) {
    $date  = substr($inputdate,0,2);
    $month = substr($inputdate,3,2);
    $year  = substr($inputdate,6,4);
    $show = $year."-".$month."-".$date;
    return $show;
  }
}

Ok use this function in db itself like

selectSql = "update " + table + " set state_" + mode + "_id=1 WHERE stoping_" + mode + " < 'ChangeDateforDB(customDATE)';"

Or otherwise you will move for mysql date_format() only

Answer 5

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