Convert date format for SQL

Question

I know this question has been asked multiple times but nothing works for me.

I want to pull the date from the input and store it in my DB (MySQLi).

This is what I tried

$ses_s = $_POST['ses_date_s'];
$ses_n_s = date("Y-m-d", strtotime($ses_s));
$q = 'INSERT INTO date_t (ses_date_s,) VALUES (?)';
$stmt = mysqli_prepare($dbc, $q);
mysqli_stmt_bind_param($stmt, 's', $ses_n_s);
mysqli_stmt_execute($stmt);

But it won't work.

Date I enter: 17/07/2014 (echo $ses_s)

Date I get converted: 1970-01-01 (Unix start) ($ses_n_s)

What am I doing wrong?

P.S. How do I pull the date in revers, I want the date to be displayed d/m/Y and not Y/m/d (like in DB)?

Thanks in advance.

EDIT:

I found the problem up (thanks to Jonathan) i used dd/mm/yyyy not mm/dd/yyy

So if I can change my question how do I convert dd/mm/yyy to yyyy/mm/dd

score 2 · Accepted Answer · answered Jan 11 '15 at 00:14

Maverick you could explode the POST date which could then be put in the order you need the data:

$ses_s = $_POST['ses_date_s']; //'17/07/2014';
$sespieces = explode("/", $ses_s);
$ses_n_s = $sespieces[2]."-".$sespieces[1]."-".$sespieces[0]; //2014-07-17

score 1 · Answer 2 · answered Jan 11 '15 at 00:01

You have two issues with the code.

1) issue with the insert statement, there's an errornous , after ses_date 2) date/time format passed in incorrectly.

$q = 'INSERT INTO date_t (ses_date_s,) VALUES (?)';

should be

$q = 'INSERT INTO date_t (ses_date_s) VALUES (?)';

The strtotime considers the locale, passing dd/mm/yyyy (canadian standard) isn't widely recognized, it's expecting it to be mm/dd/yyyy (us standard) in most cases.

I would recommend that you don't rely on strtotime to create timestamps, and would pass in the data in the mysql preferred format, or pass in an actual timestamp itself.

As to actually get the date from the database, strtotime will work fine on data from mysql as it's going to be recognized correctly.

So when you pull from the db you can do this.

echo date('d/m/Y' strtotime($date));

Don't mind the (,) there by accident because I have more values but I deleted them for a clear code. I found the problem, I inputted the date dd/mm/yyyy not mm/dd/yyyy... So if I can change my question how do I convert dd/mm/yyy to yyyy/mm/dd — Maverick, Jan 11 '15 at 00:05

score 0 · Answer 3 · answered Jan 11 '15 at 00:07

$_POST['ses_date_s'] is a string but date() requires an integer (Unix timestamp). You can convert the former to the latter using strtotime() although it only expects dates to follow the American convention of mm/dd/yy. The DateTime object has more sophisticated facilities for parsing dates.

Convert date format for SQL

3 Answers3