CODEWITHSUNDEEP
phpmysql
Mohamed Khamis
Mohamed Khamis

Reputation: 8029

Converting php date to fit mysql database

I want to convert an input date in the form of dd/mm/yyyy to the MySQL format which is yyyy-mm-dd.
I was trying to use date('Y-m-d', strtotime($_POST['date'])) but the problem is that the output is always Y-d-m, I think because it considers my 2nd argument to be mm/dd/yyyy.

How do I solve that?

Upvotes: 2

Views: 7752

Answers (6)

dqhendricks
dqhendricks

Reputation: 19251

date('Y-m-d', strtotime(str_replace('/', '-', $_POST['date'])))

Upvotes: 5

Daniel Hunter
Daniel Hunter

Reputation: 2866

I found a pretty simple conversion.

$YOUR_DATE_FORMAT YYYY/MM/DD  

$date = strtotime($YOUR_DATE_FORMAT); 

$newdate = date('Y-m-d', $date); //or whatever format you choose.

works like a charm.

Upvotes: 0

Ben Duffin
Ben Duffin

Reputation: 1058

Although this is a bit late : If he uses timestamps then, in my experience he will run into trouble if he tries to perform any MySQL Date arithmetic / calculations on the timestamps - and the over head to do the same in PHP has the potential to become very expensive as it would involve selecting ALL records and then performing comparisons / calculations on the converted dates.

And I concur with jasonbar - PHP is looking at the delimiters of the date and considers it to be a US format date! He will need to run a str_replace('/','-',$_POST['date']) BEFORE using the date() function.

So, to fix this on an incoming request:

$mysqldate = date('Y-m-d', str_replace('/','-',strtotime($_POST['date'])));

So long as the data type for the target column is datetime anyways!

Upvotes: 0

M.W. Felker
M.W. Felker

Reputation: 4823

Mohamed,

I would recommend not even formatting the date in the database. If you store all of your date / time values as UNIX TIMESTAMP, you can format the data any way you want after you pull it from the data base.

Here's why: If all of your dates are formatted and you need to compare them, you'd need to bring them back to UNIX TIMESTAMP anyways. Yes, there are wheres to compare formatted date strings but its just one more extra step.

Upvotes: 0

Felix Kling
Felix Kling

Reputation: 816422

You could do:

$date = implode('-', array_reverse(explode('/', trim($_POST['date']))));

Reference: trim, explode, array_reverse, implode

(trim might not be necessary)

Upvotes: 1

jasonbar
jasonbar

Reputation: 13461

From the manual:

Dates in the m/d/y or d-m-y formats are disambiguated by looking at the separator between the various components: if the separator is a slash (/), then the American m/d/y is assumed; whereas if the separator is a dash (-) or a dot (.), then the European d-m-y format is assumed.

You need to convert your delimiters from / to -.

Upvotes: 3

Related Questions