Date Format Update Issue

pells22

I am having difficulty converting a date to the format SQL needs during update. The user picks the date using a date picker and this is entered in standard US format. When I save it I want to convert it to the format SQL stores it, but no matter what I have tried it is not working. Here is my current code. I know the date conversion line is correct, but it is not saving to the database, I just get a null?

{
for($i=0;$i<$totalRows_RcdScheduleByMonth;$i++){

$updateSQL = sprintf("UPDATE schedule SET awayteam=%s,hometeam=%s,vs=%s,olddate=%s WHERE id=%s",
GetSQLValueString($POST['awayteam'][$i], "text"),
GetSQLValueString($POST['hometeam'][$i], "text"),
GetSQLValueString($POST['verse'][$i], "text"),
date("Y-m-d",strtotime($POST['a'][$i])),
GetSQLValueString($_POST['Id'][$i], "int"));

mysql_select_db($database_LAMSA13, $LAMSA13); $Result1 = mysql_query($updateSQL, $LAMSA13) or die(mysql_error());

$updateGoTo = "Schedule_ByMonth.php";
if (isset($SERVER['QUERY_STRING'])) {
$updateGoTo .= (strpos($updateGoTo, '?')) ? "&" : "?";
$updateGoTo .= $SERVER['QUERY_STRING'];
}
header(sprintf("Location: %s", $updateGoTo));

}
}

bradgrafelman

Can you give us an example of the format of the date your "date picker" uses?

Also, most "date pickers" I've seen allow you to specify the format used when POST'ing the date (which can certainly be different than what is displayed to the user). Are you sure yours doesn't have this option?

EDIT: Also, note that one of the main problems I see in your code (regardless of the above) is that you haven't surrounded the date value with quotes in your SQL query string.

pells22

I am using the jquery date picker and I have set the date to

$('.datepicker').live("click", function() {
    $(this).datepicker({
        changeMonth: true,
        changeYear: true,
        gotoCurrent: true,
        dateFormat: 'mm-dd-y',
    }).datepicker('show');
});

I figure it has to do with the syntax, but I am struggling to figure it out.

Thanks

bradgrafelman

You could eliminate the need to convert the date by using the altFormat property. (EDIT: Note that you should still consider the issue of sanitizing the data, however, since there's no guarantee the user even used your datepicker to provide that value.)

Either way, the syntax issue is as I mentioned above - you need to surround the date value with quotes in your SQL query string just like you would with any other string value.

pells22

After all that is looks like my format of the initial date was causing the issues. I was displaying the date as m-d-y. The - was causing all the problem. I changed the format to m/d/y and it works fine now.