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

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 开发者_StackOverflow社区how can I convert it properly?

$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

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);

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'...

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

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

or just use MySQL' DATE_FORMAT

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

Why don't you use the MySQL function directly?

insert tbl (id, other, datecol)
values(NULL, 'abcdef', CURDATE());

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

If it is not CURDATE() you want, but is a PHP variable in dd/mm/yyyy format instead, then see this MySQL man page for how to format the literals. Safe formats are YYYY-MM-DD and YYYYMMDD without time.

$date = '28/01/2008';
$query = "insert tbl (id, other, datecol)
          values(NULL, 'abcdef', '" . date('Ymd', strtotime($date)) . "');";

_{Note: yyyymmdd is also a valid format in SQL Server.}