formatting date for insert into MySQL

dugawug

well, i've read like mad about this and still can't seem to find the solution so here i am.

simple stuff, just got a form with a few date fields. i want users to enter the dates in a standard mm/dd/yy or mm/dd/yyyy format.
i understand MySQL wants yyyy-mm-dd. anyhow, the page submits and then i need to re-format the dates to use in my MySQL UPDATE query.

how can i re-format it so MySQL will take it?

i've tried the PHP date() function:

$First_Satsang_Date = date("Y-m-d", $_POST[First_Satsang_Date]);

the value for $First_Satsang_Date gets passed initially as 05/07/2003 but after going through the date() function above, it get's turned into: 1969-12-31!!!!! WTF?????

what works? i've read of a lot and i must not be doing something right...
thanks in advance..

phpsharma

Hi,

Please convert values using this..

DATE_FORMAT(date,format)
Formats the date value according to the format string. The following specifiers may be used in the format string:
example:
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%W %M %Y');
-> 'Saturday October 1997'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00', '%H:%i:%s');
-> '22:23:00'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%D %y %a %d %m %b %j');
-> '4th 97 Sat 04 10 Oct 277'
mysql> SELECT DATE_FORMAT('1997-10-04 22:23:00',
'%H %k %I %r %T %S %w');
-> '22 22 10 10:23:00 PM 22:23:00 00 6'
mysql> SELECT DATE_FORMAT('1999-01-01', '%X %V');
-> '1998 52'

If u still have doubts :please refer mysql manual..

bye..
sharma
www.manisharma.s4u.org

dugawug

well, i'm not trying to select info out of the database in a particular format, rather i'm trying to update or insert a mm/dd/yyyy formatted date.

i tried this:

UPDATE Contacts SET Empowerment_Date = DATE_FORMAT('$Empowerment_Date', '%Y-%d-%m') WHERE Email = '$_SESSION[session_email]'

$Empowerment_Date is the PHP variable which holds a value like 12/28/2003

this doesn't seem to work either...nothing gets entered into the field.

????

dugawug

ok, i R'd TFM a little...
i guess on an insert/update query I should be using STR_TO_DATE(), right?
and it's second argument should actually mirror what format i'm feeding it, right?

so i tried this:

UPDATE Contacts SET First_Satsang_Date = STR_TO_DATE('$First_Satsang_Date', '%m/%d/%Y') WHERE Email = '$_SESSION[session_email]';

again, $First_Satsang_Date is a date like 02/31/2003 so i've told it that with the %m/%d/%Y right?

i'm close but no cigar as this still doesn't work. inputs nothing. is it my quotes? i tried no quotes around my PHP variable, same.

peterdeep

Yes, I'm having the same problem. I'm a newbie and have read hoards of documents, books and watched videos until I dream about them. All anyone talks about is select queries and inserting now() in the database. I can't find anything at all about inserting a user date input into the database.

I have a text field in a form, pub_date, in which the users enter the US standard mm/dd/yy date format. The database column (MySQL) is date type. (I tried timestamp too.) The result is always 0000-00-00 and when I pull the date using a select statement I get 12/31/1969. I've tried them all - date_format, UNIX_TIMESTAMP, mktime and anything else I can find. Nothing.

I know this is a PHP and MySQL 101 question, but I'm pulling my hair out on this one. Any help at all would be appreciated. Thanks.

LordShryku

Man....after all the Y2K rewrites, you'd think people would learn to use a YYYY format.

Anyway, you need a YYYY format. Whether you make them input it, or if you can simply tack a 20 on the front, you're going to need it. Given the date 01/01/2004, you can format a MySQL date like this

$mysql_date = date('Y-m-d', strtotime($date));

peterdeep

Lord, thank you, I'll try this. This is a form field... do I need to change this to $mysql_date=date('Y-m-d',strtotime($POST['date']));? How else does the variable know which value to grab?

I use a Mac. Don't know anything about Y2K rewrites. We didn't have any. But, yes, you're right and I'll change the input format to YYYY.

LordShryku

But you do understand thee Y2K rewrites, right? The point was that everyone who was using a YY storage for the year realized that they could no longer assume 19YY, but now 20YY as well. But all the programs that were in place just assumed it as 19YY. So everyone scrambled to rewrite their programs so that new data wasn't being recorded as the year 1900 instead of 2000.

As for the variable in question, that would be correct if it's coming from a form with a post method and the name of the field is "date".

peterdeep

Thank you so much, this works great. I really appreciate it.

On second look, I was having the user input YYYY already. The Y2K thing was tongue-in-cheek, but it's good that you emphasized it here in case another reader is confused about it.

I really appreciate your help. Thanks again.