When I try to SELECT CAST('01/1/2001' AS DATE), it always returns NULL (as an example of how I can't get this to work). I'm trying to INSERT data created in this format.
How can I store dates of this format in a DATE column?
Please note that the day never has a prefix 0 while the month does if it is less than 10.
You can use MySQL function STR_TO_DATE() like this:
STR_TO_DATE('01/1/2012', '%d/%m/%Y')
Of course edit the format according to what you need.
You don't want to store it in that format. It's not very usable that way and you will regret it later when you need to do more then just read it.
Store it as a date data type...
$date = DateTime::createFromFormat("d-j-Y","01/1/2001");
$new_format = $date->format("Y-m-d"); // 2001-01-01
...and then format it when you select it:
SELECT DATE_FORMAT(`date`, "%m/%l/%Y") AS date // 01/1/2001
This will probably not be quite an answer, but if you know the exact order (d/m/Y), you can do it as follows:
list($month,$day,$year) = explode("/","01/1/2013");
$date = $year."-".str_pad($month,2,"0", STR_PAD_LEFT)."-".str_pad($day,2,"0",STR_PAD_LEFT);
You can then safely use the MySQL date format for comparisons and general checks rather than using pseudo-comparisons using VARCHAR. Not just this, but you'll also benefit from DATETIME as an index.
This is a simple way of doing it:
list($month, $day, $year) = explode("/","01/1/2013");
$time = mktime(0,0,0,intval($month),intval($day),intval($year));
$date_format = date("Y-m-d", $time);
just use this code
$date="01/1/2013";
date('Y-m-d',strtotime($date))