Help with date format

Report · Dec 14, 2009

Hi, I have a record update form that shows Start date and End date in 2 separate fields like dd/mm/yyyy. When I edit the dates they do not get to the database correctly, all I get is 0000/00/00. I guess this is because the date format accepted by MySQL is yyyy/mm/dd.

I'm stuck because I don't know how to convert the dates back to MySQL format for updating.

This is the code that displays the start date:

//<input name="AG_fechai" type="text" class="CP_loginFormFields" value="<?php echo date('d/m/Y',strtotime($row_eventosUpdate_RS['AG_fechai'])); ?>" size="32" />

This is the code that displays the end date:

//<input name="AG_fechaf" type="text" class="CP_loginFormFields" value="<?php echo date('d/m/Y',strtotime($row_eventosUpdate_RS['AG_fechaf'])); ?>" size="32" />

This is my update query.(AG_fechai and AG_fechaf are the start and end date fields) which are not been updated. I appreciate your help. Thanks.

// $updateSQL = sprintf("UPDATE t_agenda SET AG_fechai=%s, AG_fechaf=%s, AG_precio=%s, AG_horario=%s, AG_titulo_esp=%s, AG_titulo_eng=%s, AG_titulo_ger=%s, AG_titulo_fra=%s WHERE id_AG=%s",
GetSQLValueString($_POST['AG_fechai'], "date",
GetSQLValueString($_POST['AG_fechaf'], "date",
GetSQLValueString($_POST['AG_precio'], "text",
GetSQLValueString($_POST['AG_horario'], "text",
GetSQLValueString($_POST['AG_titulo_esp'], "text",
GetSQLValueString($_POST['AG_titulo_eng'], "text",
GetSQLValueString($_POST['AG_titulo_ger'], "text",
GetSQLValueString($_POST['AG_titulo_fra'], "text",
GetSQLValueString($_POST['id_AG'], "int");

mysql_select_db($database_amat_connect, $amat_connect);
$Result1 = mysql_query($updateSQL, $amat_connect) or die(mysql_error());

Report · Dec 15, 2009

MySQL stores dates in one format only: the ISO recommended YYYY-MM-DD. You need to reorder the date parts to store the date correctly.

If your input date is in the following format: dd/mm/yyyy, you can convert it by creating a custom function like this:

function convertDate($val) {
  $parts = explode('/', $val);
  return "$parts[2]-$parts[1]-$parts[0]";
}

Pass both dates to this function:

GetSQLValueString(convertDate($_POST['AG_fechai']), "date",
GetSQLValueString(convertDate($_POST['AG_fechaf']), "date",