Hi there.

Hope somebody can advise on the following:

I have created a table into my database which has a coloumn with datatype defined as DATE. The normal MySQL date order is YYYY-MM-DD, however I need to enter it in format DD-MM-YYYY.

I am using php to insert data into my table, and I was wondering at what point should I convert the date format.

To summerize, I am asking the following:

1. Is it possible to insert into MySQL database where field is formatted as DATE, a date in format DD-MM-YYYY instead in format YYYY-MM-DD?
2. If not possible, can I convert a value posted from PHP in format DD-MM-YYYY into the format YYYY-MM-DD using the MySQL syntax or I should use PHP to post the value in YYYY-MM-DD format?

Thank you in advance

I think that you should store your date in the date field in your table and then when you get that field you you format it with the functions: day(), month() and year()
ex:
select concat(day(your_date), '-', month(your_date),'-', year(your_date)) from your_table;

I strongly think REDSA has a point there. Though, i use JDBC ,servlet to be precise, and i notice that one can only retrieve date in the format DD:MM:YYYY with the appropriate JDBC code without the ability to insert it in exactly that format (DD:MM:YYYY) because MySql does not provide special codes for doing that.

cheers!

JNORA

Hi.

This is a function to convert date from DD-MM-YYYY to YYYY-MM-DD :


If you are using PHP 5 >= 5.1.0 there is native funciton :

date_format()

You can use it like this :



- Mitko Kostov

Note that is not advisable to use an in built value as your field in either programming script nor in a database you can try to change the field name DATE to some thing else

I'm registering just to inform all of you that you're missing the point.

I'm enforcing the same problem Rhyan encountered and while searching for a quick response I came accross here.

Note that he is talking about STORING, not RETRIEVING.

It's really easy to convert, transform, display and do whatever you want with a DATE retrieved from MySQL. For instance, what I do here to show it in DD-MM-YYYY format is to retrieve the value with the funcion UNIX_TIMESTAMP from MySQL and then show it with my PHP form using DATE('d-m-Y', 'valueretrievedfrommysqlwithunix_timestamp').

The problem comes when I have a form with a date field wich need to be in YYYY-MM-DD format. You would get, in instance, with PHP, a variable with a value like this: '25-12-2007' (that would be Christmas day ). How do I store that in the database? For PHP it is a string, and there's nothing to convert a string to a date.

Note that the date is entered manually by the user, I don't get it automatically with a date() or whatever.

>The problem comes when I have a form with a date field wich need to be in YYYY-MM-DD format. You would get, in instance, with PHP, a variable with a value like this: '25-12-2007' (that would be Christmas day ). How do I store that in the database? For PHP it is a string, and there's nothing to convert a string to a date.Note that the date is entered manually by the user, I don't get it automatically with a date() or whatever.

Well obviously the ideal solution would be to convert '25-12-2007' to '2007-12-25' immediately then store it in the database.

If you are allowing the user to enter it in, then you need to validate it is entered in the correct format. A simple regular expression would suffice here. Presumably anyone using mysql is using it with another more flexible programming language, for example, php where regular expressions can be used.

I already have the answer; it was not difficult at all, but it was just too late to post it .

The format DD-MM-YYYY is the correct one in my case; that's the estandard European format.

As you noted, I have to convert it to MySQL's "internal" format. There is a simple function in MySQL wich accomplishes that: str_to_date. As the name says, it will convert a string to a date. You can specify how it is formatted using the standard way in MySQL. In my case, I would do this:

str_to_date('".$mydate."', '%d-%c-%Y')

That should go in the SQL sentence, since it is a MySQL function, not PHP. For instance, if you are doing an UPDATE:

UPDATE mytable SET data_formatted=str_to_date('".$mydate."', '%d-%c-%Y') WHERE id=".$condition

I hope it helps.