How to change the date format?

[Wayniac]

Hello everyone,

 

I'm attempting to convert my date format for mysql database from YYYY-MM-DD to MM-DD-YYYY. It seems to say it works to my little understanding of this process, but nothing changes.

 

This is the SQL code I used in phpmyadmin.

SELECT DATE_FORMAT(CURDATE(), '%m/%d/%Y')

 

This is the message I got afterwards.

06/30/2010

 

If I browse that field in phpmyadmin, it still shows the following format:

0000-00-00

 

Any suggestions? Thank you everyone.

[PFMaBiSmAd]

You format the data the way you want it to be in a query. It always remains the YYYY-MM-DD format in the database table.

[Wayniac]

Thank you for your quick reply PFMaBiSmAd.

 

So do you mean I would have to run this through a php query? Also, do you have enther an example or know of any tutorials that demonstrate this. I'm having a hard time finding such a thing, I figured this would be a pretty standard procedure given how common it is on the web.

[PFMaBiSmAd]

From your first post in this thread -

SELECT DATE_FORMAT(your_date_column_name_here, '%m/%d/%Y')

[Wayniac]

If I attempt to paste the following into the phpmyadmin SQL section:

SELECT DATE_FORMAT(date_ship, '%m/%d/%Y')

 

It gives me this error:

Error

SQL-query : 

SELECT DATE_FORMAT( date_ship, '%m/%d/%Y' )

MySQL said:

Unknown column 'date_ship' in 'field list'

Back

 

There is indeed a field with this exact spelling "date_ship" in the table.

[PFMaBiSmAd]

A complete query would be -

 

SELECT DATE_FORMAT(date_ship, '%m/%d/%Y') FROM your_table

[Wayniac]

Once again, thank you so much. But was I suppose to run this query through a php code?

 

This is what happens when I enter it in the phpmyadmin SQL section:

DATE_FORMAT( date_ship, '%m/%d/%Y' )
00/00/0000

 

Which is great, but when I browse the fields section, its still 0000/00/00? You said earlier the database format never changes, how would I approach this then?

 

Thank you

[PFMaBiSmAd]

The msyql DATE_FORMAT() function ONLY takes the values you give it as arguments and returns the formatted value. You use it in any query that you want to get a standard YYYY-MM-DD DATE value into any other format.

[Wayniac]

Sorry, I'm not usually this confused, perhaps I'm just really tired. Could you tell me how I can get my field "ship_date" which is on my webpage that sends off the data to my mysql database when submitted to either be stored as MM-DD-YYYY or when I try an retrieve it on my other page that views the entry to be displayed as MM-DD-YYYY.

 

Sorry if I'm making this difficult for you, I really do appreciate you helping me.

[PFMaBiSmAd]

how I can get my field "ship_date" which is on my webpage that sends off the data to my mysql database when submitted to either be stored as MM-DD-YYYY

 

^^^ You don't. You take the MM-DD-YYYY submitted value and you reformat it as a standard YYYY-MM-DD value to be inserted into your table. Since you are probably breaking the submitted value into its' individual parts in order to validate it, it is easiest to simply put it back together in the correct format. Another way to change a MM-DD-YYYY format into a YYYY-MM-DD format would be to use the mysql STR_TO_DATE() function in your INSERT query.

 

... when I try an retrieve it on my other page that views the entry to be displayed as MM-DD-YYYY.

 

^^^ That would be where you would use the the DATE_FORMAT() function in a query.

 

 

 

[Wayniac]

I think I understand now. You definatly set me in the right direction. I will have to do more research, otherwise I'm just typing and not learning. Once again, thank you so much