PHP mysql insert date format

by

As stated in Date and Time Literals:

MySQL recognizes DATE values in these formats:

  • As a string in either 'YYYY-MM-DD' or 'YY-MM-DD' format. A “relaxed” syntax is permitted: Any punctuation character may be used as the delimiter between date parts. For example, '2012-12-31', '2012/12/31', '2012^12^31', and '2012@12@31' are equivalent.

  • As a string with no delimiters in either 'YYYYMMDD' or 'YYMMDD' format, provided that the string makes sense as a date. For example, '20070523' and '070523' are interpreted as '2007-05-23', but '071332' is illegal (it has nonsensical month and day parts) and becomes '0000-00-00'.

  • As a number in either YYYYMMDD or YYMMDD format, provided that the number makes sense as a date. For example, 19830905 and 830905 are interpreted as '1983-09-05'.

Therefore, the string '08/25/2012' is not a valid MySQL date literal. You have four options (in some vague order of preference, without any further information of your requirements):

  1. Configure Datepicker to provide dates in a supported format using an altField together with its altFormat option:

    <input type="hidden" id="actualDate" name="actualDate"/>

    $( "selector" ).datepicker({
    altField : "#actualDate"
    altFormat: "yyyy-mm-dd"
});

    Or, if you’re happy for users to see the date in YYYY-MM-DD format, simply set the dateFormat option instead:

    $( "selector" ).datepicker({
    dateFormat: "yyyy-mm-dd"
});

  2. Use MySQL’s STR_TO_DATE() function to convert the string:

    INSERT INTO user_date VALUES ('', '$name', STR_TO_DATE('$date', '%m/%d/%Y'))

  3. Convert the string received from jQuery into something that PHP understands as a date, such as a DateTime object:

    $dt = \DateTime::createFromFormat('m/d/Y', $_POST['date']);

    and then either:

    • obtain a suitable formatted string:

      $date = $dt->format('Y-m-d');

    • obtain the UNIX timestamp:

      $timestamp = $dt->getTimestamp();

      which is then passed directly to MySQL’s FROM_UNIXTIME() function:

      INSERT INTO user_date VALUES ('', '$name', FROM_UNIXTIME($timestamp))

  4. Manually manipulate the string into a valid literal:

    $parts = explode("https://stackoverflow.com/", $_POST['date']);
$date  = "$parts[2]-$parts[0]-$parts[1]";

Warning

  1. Your code is vulnerable to SQL injection. You really should be using prepared statements, into which you pass your variables as parameters that do not get evaluated for SQL. If you don’t know what I’m talking about, or how to fix it, read the story of Bobby Tables.

  2. Also, as stated in the introduction to the PHP manual chapter on the mysql_* functions:

    This extension is deprecated as of PHP 5.5.0, and is not recommended for writing new code as it will be removed in the future. Instead, either the mysqli or PDO_MySQL extension should be used. See also the MySQL API Overview for further help while choosing a MySQL API.

  3. You appear to be using either a DATETIME or TIMESTAMP column for holding a date value; I recommend you consider using MySQL’s DATE type instead. As explained in The DATE, DATETIME, and TIMESTAMP Types:

    The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

    The DATETIME type is used for values that contain both date and time parts. MySQL retrieves and displays DATETIME values in 'YYYY-MM-DD HH:MM:SS' format. The supported range is '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.

    The TIMESTAMP data type is used for values that contain both date and time parts. TIMESTAMP has a range of '1970-01-01 00:00:01' UTC to '2038-01-19 03:14:07' UTC.

More Related Contents:

  1. Php date() Date and time is stuck [closed]
  2. Using PHP read word in textarea line by line and insert to mySQL
  3. How to insert multiple rows from array using CodeIgniter framework?
  4. Incorrect Integer (2147483647) is inserted into MySQL?
  5. Escaping single quote in PHP when inserting into MySQL [duplicate]
  6. PHP/MySQL Insert null values
  7. MySQL – ignore insert error: duplicate entry
  8. Calculate age based on date of birth
  9. Object of class DateTime could not be converted to string
  10. Set timezone in PHP and MySQL
  11. How to convert ISO8601 to Date format in php
  12. php run once and insert twice in mysql database
  13. How to display a date as iso 8601 format with PHP
  14. Date in a URL dd/mm/yyyy
  15. Change date format (in DB or output) to dd/mm/yyyy – PHP MySQL
  16. mysql – INSERT date range into date columns IF dates don’t overlap with existing ones
  17. PHP mySQL – Insert new record into table with auto-increment on primary key
  18. Formatting an SQL timestamp with PHP
  19. adding 1 day to a DATETIME format value
  20. Replacing \r\n with PHP
  21. MySQL, how to insert null dates
  22. Query database then email posts from where user is subbed to
  23. Correct way to use LIKE ‘%{$var}%’ with prepared statements?
  24. Concatenating a string and primary key Id while inserting
  25. Php : Convert a blob into an image file
  26. How do I construct a cross database query in PHP?
  27. Having issue with matching rows in the database
  28. Create PHP array from MySQL column
  29. How do i search from serialize field in mysql database?
  30. What is the PDO equivalent of function mysql_real_escape_string?

Leave a Comment