Jump to content


Photo

? about formatting date for insert to mysql database


  • Please log in to reply
4 replies to this topic

#1 drranch

drranch
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 07 September 2006 - 03:36 AM

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?

#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 07 September 2006 - 12:42 PM

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.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 07 September 2006 - 12:51 PM

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?


Is it in "mm dd yy" format or %b %d %Y as defined for the DATE_FORMAT() function?

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

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.
$date = "01 31 1999';
$date = substr($date, -4).'-'.substr($date, 0, 2).'-'.substr($date, 3, 2);
echo $date;

You can also look at PHP's strtotime and date functions to do the conversion.

#4 drranch

drranch
  • Members
  • PipPipPip
  • Advanced Member
  • 78 posts

Posted 07 September 2006 - 03:12 PM

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

#5 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 07 September 2006 - 04:06 PM

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

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

EDIT: If you're dealing with dates before 1970 then you can use explode  and an array with the month integer values that correspond to the 3 letter month abbreviations and create the date from that.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users