Inserting date in format DD-MM-YYYY in MySQL

[Rhyan]

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

[redsa]

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;

[jnora]

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

[MitkOK]

Hi.

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

function con2mysql($date) {

  $date = explode("-",$date);
  if ($date[0]<=9) { $date[0]="0".$date[0]; }
  if ($date[1]<=9) { $date[1]="0".$date[1]; }
  $date = array($date[2], $date[1], $date[0]);
 
 return $n_date=implode("-", $date);
 }

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

date_format()

You can use it like this :

$date ='12-12-2007';
$dateTime = new DateTime($date);

$formatted_date=date_format ( $dateTime, 'Y-m-d' );

echo $formatted_date;
// This will output 2007-12-12

- Mitko Kostov

[atplerry]

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

[Cacauo]

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 :P). 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.

[iamthwee]

>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 :P). 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.

[Cacauo]

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 :P.

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.

[rori]

I was having a similar issue and I just wanted to post the solution I used in case it could be useful to anyone.

My situation:
I'm inserting the dates using PHP in MySQL database in the format:
Y-m-d H:i:s (which displays date as: 2010-03-13 10:32:10)
This value is stored in the MySQL table in a field with the type datetime in this format since I didn't know if I can change the default format in MySQL.

This is how I'm setting the date with PHP:

$date_added=date("Y-m-d H:i:s", time());

And this is how I'm inserting it in the table:

INSERT INTO MyTable(date_added) VALUES('$date_added')";

My issue:
I want to show the date on the webstie in the format:
d-m-Y H:i:s (which displays date as: 13-03-2010 10:32:10)

My solution:

<td><strong>Date added:</strong></td>
<td>
<?php
$existing_results=mysql_query("SELECT * FROM MyTable WHERE id=$id");
$existing_row=mysql_fetch_array($existing_results);
$db_date_added=$existing_row['date_added']; //get added date
echo(date('d-m-Y H:i:s', strtotime($db_date_added)));
?>
</td>

Hope it's clear and helpful.

[drjohn]

The solution to this is blindingly simple, as I use this a lot.
The user is NOT allowed to enter dd-mm-yyyy
Instead have a form with a field for day, a field for month, a field for year.
Then YOU take the year, the month and the day and build the MySQL required format. So exceedingly simple. so exceedingly fool-proof.
As we say in aviation - "you have control".

If you have designed a form where someone enters dd-mm-yyyy as a string in a single data entry box, I'm sorry but you have made a design error, an error that then causes you problems. Sort the data input form.

If the date comes not from a form but somewhere else, use explode and reconstruct the date in the correct format.

When you retrieve the date,, you have lots of formatting options in php in how you display that date. Again, you have control.

[printrobin]

Never going with redsa because this issue is related to php & I don't think that your opinion will work to overcome to this problem.