sql date format

[nashsaint]

Hi,

 

I have a problem with the date on sql.  This is the fieldtype:

 

today DATE;

 

and I use the now() function to enter todays date to sql.  I format the date to ('%d-%m%y') but checking the phymyadmin would reveal that the date was displayed the other way around, in YYYY-MM-DD format instead of dd-mm-yy.

 

How can I fix this so that sql would encode the date as dd-mm-yy?

 

Thanks.

[Dathremar]

I am not sure if you can or can't insert the date into db like dd-mm-yyyy, but a simple solution is to create a function which will format your date when you read from DB. I did that tbh

[Barand]

How can I fix this so that sql would encode the date as dd-mm-yy?

You don't. That format is totally useless as a database date.

 

It's YYYY-MM-DD for a reason.

 

You can compare dates for < or >, therefore you can sort them, you can select by date ranges and the dozens of useful datetime functions expect it to be that way.

 

with dd-mm-yy all you can do is print it.

[PFMaBiSmAd]

Short answer - the format for a DATE data type is YYYY-MM-DD.

 

A function like NOW() or CURDATE() provides a correct value. If you are inserting your own date, you either need to format it the same before you put it into your query or you need to use the mysql STR_TO_DATE() function in the query to get mysql to format it for you. To retrieve it in a different format, you either need to use the mysql DATE_FORMAT() function in your query (the fastest executing method) or you need to format it yourself after you have fetched it using php code (the slower executing method.)