How can i convert a date in php to SQL format ? (and insert it)

Question

I'm trying to insert a date , lets say CURDATE() to an sql DateTime field.

My date is in the format of: 28/01/2008

When I try to insert it to the SQL, I get just zeroes.

So how can I convert it properly?

Answer 1

$new_date = date('Y-m-d', strtotime($old_date));

Explanation

1. strtotime() will try to parse a string ($old_date in this case) and understand what date it is. It expects to be given a string containing an English date format or English textual datetime description. On success it will return a Unix timestamp (the number of seconds since January 1 1970). Now we have got a point in time out of that string.

2. date() then will turn this previously obtained point in time to a format, described in the first parameter, in the example above it is the 'Y-m-d'

   • Y — A full numeric representation of a year, 4 digits
   • m — Numeric representation of a month, with leading zeros
   • d — Day of the month, 2 digits with leading zeros
   • - — literally the minus symbol

Here's a full list of characters you can use in the format parameter string

Answer 2

I'm trying to insert a date , lets say CURDATE() to an sql DateTime field.

$timestamp = strtotime($old_date);
$mydate = date('Y-m-d H:i:s', $timestamp);

Answer 3

Since you're using the European date notation (dd/mm/yyyy) the strtotime function will return 0 (which in turn will be converted to 1970-01-01 by date) if you don't use - or . as separator.

So if you want to use strtotime, then you will have to change your date strings just a bit :

$olddate = '28/01/2008';
$newdate = strtotime(str_replace('/', '-', $olddate));

$newdate should now contain '2008-01-28'...

Answer 4

join('-',array_reverse(explode('/',$date)))

to get the standard YYYY-MM-DD Mysql date format.

or just use MySQL' DATE_FORMAT