Mysql Date Format

Hi guys

There's another one.

I remember finding once how to do it around 2 years ago, unfortunately could not do it now.

I need to change format in which MySQL stores dates to dd-mm-yyyy.

I know that formats can be changed in the queries, but it will be taking up extra cycles on the server, doing it once seems more apropriate, as my server system is SLOW.

You mean this: http://dev.mysql.com/doc/refman/5.0/...functions.html

Quote:

---

Originally Posted by MsMittens

You mean this: http://dev.mysql.com/doc/refman/5.0/...functions.html

---

This one is used with Select statement.

And I am looking to change the way MySQL stores dates in the DATE field, from yyyy-mm-dd 00:00:00 to dd-mm-yyyy

I don't think that you can do it that way?

My understanding is that the date is actually stored in a standard format (yyyy-mm-dd) and that you can change this with a format or a function to display it?

I could be wrong, but I think that is how more recent versions do it, at least

This may help: http://www.dan.co.uk/mysql-date-format/

Or don't do it via the select statement and do something like this:

Code:

---

//some code to get the ResultSet...
Timestamp stamp = results.getTimestamp(3);//or whatever index
Date d = new Date(stamp.getTime());
SimpleDateFormat sdf = new SimpleDateFormat("dd-MM-yyyy");
String k = sdf.format(d);

---

I guess I'll need to delve in my notes more then 2 years back and see if I can find it there

I have a LOT of notes, it will take a LONG time :(

I'm going to get fired till I find the answer :)

Hmmm,

Quote:

---

I know that formats can be changed in the queries, but it will be taking up extra cycles on the server, doing it once seems more apropriate, as my server system is SLOW.

---

If that is the case I would say that you have a much bigger problem with your server and database/applications other than the way MySql naturally handles dates.

I would question whether MySql would actually store the date as dd-mm-yyy rather that just do an on the fly conversion when you recall it?

I wonder if you are thinking of Sql Server, which I seem to recall does/did store dates in that format?

There might be something in DBFormat that would help?

Quote:

---

Originally Posted by nihil

Hmmm,


If that is the case I would say that you have a much bigger problem with your server and database/applications other than the way MySql naturally handles dates.

I would question whether MySql would actually store the date as dd-mm-yyy rather that just do an on the fly conversion when you recall it?

I wonder if you are thinking of Sql Server, which I seem to recall does/did store dates in that format?

There might be something in DBFormat that would help?

---

I'll look in to "DBFormat".

Yet the problem is not within the server itself, but making the aplication as efficient as possible.

As the system is going to be aimed at Europe and might be getting a number of hits it only makes sence to change the format of the dates once, instead of doing it for a hundred users, with a up to 20 - 30 dates. Only that will make it 2000 - 3000 conversions. If the amount of visitors grows and the amount of data expands... I don't think that you need me to do the maths ;).

I don't think you can do it like that. MySQL stores the date in the standard (ISO) format yyyy-mm-dd. You don't have any options to change it (at least, that was always my understanding).
As has been previously stated, your best bet is to convert on entry/retreival by using DATE_FORMAT⁠(⁠).

2 links for you...
http://dev.mysql.com/doc/refman/5.0/...functions.html
http://safari.oreilly.com/059652708X...X_CHP_6_SECT_3

I also do not know anyway to store dates differently. You can format the query and that should be all that most people need. That link that nihil works great :-)