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?
Add a comment
|
5 Answers
$new_date = date('Y-m-d', strtotime($old_date));
Explanation
strtotime() will try to parse a string (
$old_datein 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.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 digitsm— Numeric representation of a month, with leading zerosd— 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
4 Comments
Yochai Timmer
strtotime() isn't returnning anything.
Yochai Timmer
Ok, the strtotime just doesn't work on that format... need to change the '/' to '-'
MatTheCat
I think strtotime doesn't understand the french date format..! You can use my code...
wimvds
Look at my answer below, it's because strtotime expects US date format when you're using / as a separator, replacing it will indeed help. Refer to PHP Reference : date formats for more information.
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);
Comments
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'...
Comments
join('-',array_reverse(explode('/',$date)))
to get the standard YYYY-MM-DD Mysql date format.
or just use MySQL' DATE_FORMAT
Comments
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.