? about formatting date for insert to mysql database

[drranch]

Hello all!

I need education on how to format a date so mysql will accept the information.

I have a form that gathers a date from the customer.  The customer can either enter the date or use a java script pop up calendar.  The Java pop up calendar displays the date as "mm dd yy" format.

Mysql will not accept this information, so how do I go about converting the "%%b %%d, %%Y" to "YYYY-MM-DD" in my sql insert script?

[fenway]

There's a STR_TO_DATE() function that can take an almost arbitrary format specifier.  Of course, the pop-up should be producing a valid SQL date anyway, but it sounds like you're not intending to change it.

[shoz]

[quote author=drranch link=topic=107193.msg429697#msg429697 date=1157600163]
Hello all!

I need education on how to format a date so mysql will accept the information.

I have a form that gathers a date from the customer.  The customer can either enter the date or use a java script pop up calendar.  The Java pop up calendar displays the date as "mm dd yy" format.

Mysql will not accept this information, so how do I go about converting the "%%b %%d, %%Y" to "YYYY-MM-DD" in my sql insert script?
[/quote]

Is it in "mm dd yy" format or %b %d %Y as defined for the [url=http://dev.mysql.com/doc/refman/4.1/en/date-and-time-functions.html]DATE_FORMAT()[/url] function?

As fenway mentioned you can use STR_TO_DATE if you're using MYSQL 4.1 or higher.
[code]
INSERT INTO tablename (col1, date_col, ...) VALUES(col1_val, STR_TO_DATE('date_here', '%m %d %Y'), ...);
[/code]

If you haven't already used MYSQL 4.1 and higher features then you'd probably want to use PHP to convert the date so that you don't have to rely on having a recent MYSQL installation.
[code]
$date = "01 31 1999';
$date = substr($date, -4).'-'.substr($date, 0, 2).'-'.substr($date, 3, 2);
echo $date;
[/code]

You can also look at PHP's [url=http://www.php.net/strtotime]strtotime[/url] and [url=http://www.php.net]date[/url] functions to do the conversion.

[drranch]

%b %d %Y is whats defined for the DATE_FORMAT() function...

[shoz]

You should be able to use the STR_TO_DATE example if you change %m to %b.

php example
[code]
$date = "sep 30 2000";
$date = date('Y-m-d', strtotime($date));
print $date;
[/code]

EDIT: If you're dealing with dates before 1970 then you can use [url=http://www.php.net/explode]explode[/url]  and an array with the month integer values that correspond to the 3 letter month abbreviations and create the date from that.