[ACCEPTED]-How to validate a MYSQL Date in PHP?-date

Accepted answer
Score: 22

I personally found this to be the correct and 2 elegant way to determine if the date is 1 both according to format and valid:

  • treats dates like 20111-03-21 as invalid - unlike checkdate()
  • no possible PHP warnings (if any one parameter is provided, naturally) - unlike most explode() solutions
  • takes leap years into account unlike regex-only solutions
  • fully compatible with the mysql DATE format (10.03.21 is the same as 2010-03-21)

Here's the method you can use:

/**
 * Tests if a string is a valid mysql date.
 *
 * @param   string   date to check
 * @return  boolean
 */
function validateMysqlDate( $date )
{
    return preg_match( '#^(?P<year>\d{2}|\d{4})([- /.])(?P<month>\d{1,2})\2(?P<day>\d{1,2})$#', $date, $matches )
           && checkdate($matches['month'],$matches['day'],$matches['year']);
}
Score: 6

I am using this function:

<?php 
function validateMysqlDate( $date ){ 
    if (preg_match("/^(\d{4})-(\d{2})-(\d{2}) ([01][0-9]|2[0-3]):([0-5][0-9]):([0-5][0-9])$/", $date, $matches)) { 
        if (checkdate($matches[2], $matches[3], $matches[1])) { 
            return true; 
        } 
    } 
    return false; 
} 

// check it: 
  $a = validateMysqlDate('2012-12-09 09:04:00');
  $b = validateMysqlDate('20122-12-09 09:04:00');
  $c = validateMysqlDate('2012-12_09 09:04:00');
  $d = validateMysqlDate('');
  var_dump( $a );
  var_dump( $b );
  var_dump( $c );
  var_dump( $d ); 
?>

$a is true, the others are false - and that 5 is correct

The function from Raveren (above) will 4 not cover valid dates with timestamps !!! $a 3 returns false there! And btw: checkdate() would 2 return true for $b although it is not a 1 valid mysql datetime

Score: 5

If they are 3 separate drop-downs, you will 8 need to validate them as three separate 7 values.

Ie,

  • Validate that the year column is numeric and between whatever years are valid in your app
  • Validate that the month column is numeric
  • Validate that the day column is numeric
  • Validate that they are all valid values using checkdate()

Or, you could just cast them 6 all to integer, combine them together into 5 a date, and see if the resulting date is 4 valid. Ie,

$time = mktime(0, 0, 0, (int)$_POST['month'], (int)$_POST['day'], (int)$_POST['year']);

// in this example, valid values are between jan 1 2000 (server time) and now
// modify as required
if ($time < mktime(0, 0, 0, 1, 1, 2000) || $time > time())
  return 'Invalid!';

$mysqltime = date('Y-m-d', $time);

// now insert $mysqltime into database

The downside to this method is 3 that it'll only work with dates within the 2 Unix timestamp range ie 1970 to 2038 or 1 so.

Score: 2

You can check that the date is valid using 4 checkdate. If you want to make sure that the values 3 are numeric and the correct length, you 2 could do something as simple as an is_int ctype_digit and 1 a strlen combination before you build the date.

// untested
if( !ctype_digit)( $month ) || strlen( $month ) != 2 ) {
    // handle error
}
// repeat for $day and $year
if ( checkdate( $month, $day, $year ) {
    // do your work
}
Score: 1

Just had the same issue. I wrote a short 2 function that checks if the format is correct, and 1 also if the date is real:

function valid_mysql_date($str){
    $date_parts = explode('-',$str);
    if (count($date_parts) != 3) return false;
    if ((strlen($date_parts[0]) != 4) || (!is_numeric($date_parts[0]))) return false;
    if ((strlen($date_parts[1]) != 2) || (!is_numeric($date_parts[1]))) return false;
    if ((strlen($date_parts[2]) != 2) || (!is_numeric($date_parts[2]))) return false;
    if (!checkdate( $date_parts[1], $date_parts[2] , $date_parts[0] )) return false;
    return true;
}

hope it helps.

More Related questions