I cannot seem to find any info on this..
I need to convert a string to a date so that it will import properly to an SQL DATE field. When I import 12/25/2012 to the DB, it appears as 0000-00-00.
What's the proper way to do this?
Links and refs appreciated.
MySQL accepts dates in this format YYYY-MM-DD either change your date format 12/25/2012 to 2012-12-25 or modify them to match the correct format.
EDIT If you want to continue using your own format try this
list($d,$m,$y) = explode("/", "12/25/2012"); //replace 12/25/2012 with your date
$hyphenDate = $y . '-' . $m . '-' . $d;
echo $hyphenDate;
As @Ravi pointed out in his answer, MySQL accepts dates in the format YYYY-MM-DD. Quoted from 11.1.5. Date and Time Types1:
Although MySQL tries to interpret values in several formats, date parts must always be given in year-month-day order
For this, you can use str_todate()2 function to format it:
str_to_date('12/25/2012', '%m/%d/%Y);
This way, these input strings will be stored in your database as date objects(without any specific date format). Later, if you want to output these dates in a specific format you can use DATE_FORMAT3 to format it. Something like:
SELECT DATE_FORMAT(datefield, '%Y-%m-%d') FROM Test;
--2012-12-25
1, 2, 3: Links and refs, that you asked for.
Use class DateTime. Examples:
$SomeDate = new DateTime();
echo $SomeDate->format( 'Y-m-d H:i:s' ); //Must be MySQL compatible (YYYY-MM-DD)
$ThisDate = new DateTime( date( 'Y-m-d H:i:s' ) );
echo $ThisDate->format( 'Y-m-d H:i:s' ); //Must be MySQL compatible (YYYY-MM-DD)
