Date Format Update Issue
Results 1 to 5 of 5

Thread: Date Format Update Issue

  1. #1
    Junior Member
    Join Date
    Dec 2012
    Posts
    5

    Date Format Update Issue

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

    }
    }

  2. #2
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,414
    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.

  3. #3
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    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

  4. #4
    Pna lbh ernq guvf¿
    Join Date
    Jul 2004
    Location
    Kansas City area
    Posts
    19,414
    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.
    Last edited by bradgrafelman; 12-30-2012 at 02:17 PM.

  5. #5
    Junior Member
    Join Date
    Dec 2012
    Posts
    5
    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.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •