Casting now

Question

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

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

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

Answer 1

MySQL's 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 returns the current date - curdate()

So your request should look like

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

But you have 2 main disadvantages (or even 3):

• the date field should be in the format YYYY-MM-DD, and not anything else
• ,

Answer 3

YYYY-MM-DD is mySQL's original date format. I find it hard to believe that comparing with the string DD-MM-YYYY works?

In any case, you are looking for DATE_FORMAT () .

Answer 4

Use any function like this to return the 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 5

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;
  }
}

It’s good to use this function in db itself as

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

Or else you will only go for mysql date_format ()