ansaurus

Question

MySQL: How to check if a string is a valid DATE, TIME or DATETIME

Answer 1

+1 A:

If you choose a server mode for the MySQL server that doesn't allow invalid date values a query containing such a malformed date representation will cause an error instead of (silently) assuming 0000-00-00
see http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

e.g.

$pdo = new PDO('mysql:host=localhost;dbname=test', 'localonly', 'localonly'); 
$pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );

$pdo->exec('CREATE TEMPORARY TABLE foo (id int auto_increment, d datetime, primary key(id))');

$query = "INSERT INTO foo (d) VALUES ('2010-02-31 12:15:18')";
foreach( array('ALLOW_INVALID_DATES', 'STRICT_ALL_TABLES') as $mode ) {
  echo $mode, ": "; flush();
  $pdo->exec("SET SESSION sql_mode='$mode'");
  $pdo->exec($query);
  echo "Ok.\n";
}

prints

ALLOW_INVALID_DATES: Ok.
STRICT_ALL_TABLES: 
Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '2010-02-31 12:15:18' for column 'd' at row 1' in [...]

VolkerK 2010-04-22 11:16:55

Good to know! Will look into setting that mode. With this I would have to actually try and then catch the exception though. Is there a way I can check it before I run the insert or update?

Svish 2010-04-22 11:46:43

None that I know of. But does it matter whether a (fictional) function mysql\_check\_date() returns false or the execution of the query fails (with a specific error code)?

VolkerK 2010-04-22 11:58:30

When I need it to validate user input, then yes, kind of. Especially if you want to do the check "client-side" using AJAX or something before submit.

Svish 2010-04-22 12:10:25

Answer 2

A:

Use this function in php to check for a valid date:

function valid_date($date) {
    return (preg_match("/^([0-9]{4})-([0-9]{2})-([0-9]{2})$/", $date));
}

Or as @VolkerK said if an invalid date is inserted into the database it will be stored as 0000-00-00 so you could also do:

SELECT * FROM table WHERE date_field > '0000-00-00'

fire 2010-04-22 11:20:33

but this function returns true with '2010-32-56'

Osman Üngür 2010-04-22 11:44:30

Yeah, that will weed out the dates in the wrong format, but doesn't take care of invalid dates. Is already using this for a pre-check though, except I use `\d` instead of `[0-9]`. And the parenthesis isn't needed AFAIK: `/^\d{4}-\d{2}-\d{2}$/`

Svish 2010-04-22 12:11:38

Answer 3

A:

You could parse the date according to the format you want to use and then call checkdate to test if it's a valid date. Make sure you read the comments on http://php.net/manual/en/function.checkdate.php.

wimvds 2010-04-22 13:14:08

Answer 4

A:

You could just use a 'constant' query, without using temporary tables and test fields:

mysql> select day('2010-02-31 00:00:00');
+----------------------------+
| day('2010-02-31 00:00:00') |
+----------------------------+
|                       NULL | 
+----------------------------+

Marc B 2010-04-24 02:46:32

ansaurus

tags:

views:

answers:

MySQL: How to check if a string is a valid DATE, TIME or DATETIME

related questions