mysql pregmatch_preg_match for mysql date format

im trying to validate a date to see if it matchs the mysql format

this is the code

$match = "/^\d{4}-\d{2}-\d{2} [0-2][0-3]:[0-5][0-9]:[0-5][0-9]$/";

$s = $this->input->post("report_start"). " " . $this->input->post("report_start_time").":00";

$e = $this->input->post("report_end"). " " . $this->input->post("report_end_time").":59";

if($this->input->post("action") != "")

{

echo trim($s). " => " . preg_match($match, trim($s));

echo "
";

echo trim($e). " => " . preg_match($match, trim($e));

}

the date format goes into $s and $e are

$s = 2011-03-01 00:00:00

$e = 2011-03-01 23:59:59

and they both return false (0).

however if i manual inter the date strings into preg_match like

preg_match($match, "2011-03-01 00:00:00")

it works.

i have no idea what im doing wrong

======================

now that i think about it, i only need to validate the houre:min part of the datetime string.

im manually adding the seconds and the date is forced by a datepicker and users cant edit it

# Answer 1

ok heres wat i did.

since im forcing the date format and the ending seconds of the time part

i just validated the hour:mini part using "/^2[0-3]|[01][0-9]:[0-5][0-9]$";

and if that returns true i put everything together end reconstructed the final datetime string

$match = "/^2[0-3]|[01][0-9]:[0-5][0-9]$/";

$s_d = $this->input->post("report_start");

$s_t = $this->input->post("report_start_time");

$e_d = $this->input->post("report_end");

$e_t = $this->input->post("report_end_time");

if($this->input->post("action") != "")

{

if(

( preg_match($match , trim($s_d." ".$s_t.":00")) )

&& ( preg_match($match , trim($e_d." ".$e_t.":59")) )

)

{

$r = $this->model_report->client_hours_logged(array($s,$e));

$data['report'] = $r;

var_dump($r);

//$this->load->view("report/client_hours_per_client",$data);

}

}

# Answer 2

Watch out:

[0-2][0-3] is not a good regex for hour values - it will match 01, 12, 23 and others, but it will fail 04 through 09 and 14 through 19.

Better use (2[0-3]|[01][0-9]) instead.

# Answer 3

You're making your work harder that it needs to be. In php there are many date handling functions that mean you don't have to treat dates like strings. So, rather than test that your input dates are in the correct format, just insist on the correct format:

$adate= date_create('January 6, 1983 1:30pm'); //date format that you don't want

$mysqldate= $adate->format("Y-m-d h:i:s");//date format that you do want

There are also functions to check that a date is a real date, like checkdate.

# Answer 4

You could use strtotime and date to parse and format the date properly.

# Answer 5

Why not just simply force the date into the format you want:

$e = '2011-03-01 00:00:00';

$mysqlFormat = date('Y-m-d H:i:s', strtotime($e));

Also, there is a bit of an error in your regex [0-2][0-3]:[0-5][0-9]:[0-5][0-9] will only match the hours of 00,01,02,03,10,11,12,13,20,21,22,23 so it will never match 4am, or 3pm among others. That aside I looked over your RegEx and I don't see any problems with it matching the test cases you've offered. I would check to make sure there is not extra whitespace on either side of date string with trim().

# Answer 6

I concur with Tim : MySQL behaves in quirks mode and always tries to go easy on DATE and DATE_TIME column types. You can omit certain parts of your input and it still will try to compensate and achieve that goal successfully to some degree... That's why, most numbers your Reg-ex considers as invalid, MySQL will accept as valid.

# Answer 7

I use this to validate a 'Y-m-d H:i:s' format date string:

match = '/^[12][0-9]{3}-(0[1-9]|1[0-2])-(0[1-9]|[1-2][0-9]|3[01]) ([01][0-9]|2[0-3]):[0-5][0-9]:[0-5][0-9]$/';