xyz

Is there any way to change the default format used for DATETIME fields within the MySQL database, rather than formatting it everytime I use it.

Default format example: 2008-01-09 14:55:30

whereas I'd like: 01-09-2008 14:55:30 or even 01/09/2008 14:55:30

I've searched the documentation/forum but may have missed it as it was late last night.

Charles

If you are using PHP you can pull the date from the DB and convert it and display it how you want to.
I am sure you could probably do the same with other programming languages.

xyz

Thanks for your reply Charles.

Sorry, yes I am using PHP.

I just wondered if there was a way to change the default format, so that I don't need to keep converting it. Whilst these date conversions aren't any paticular drain on performance, I am always looking at ways to reduce any extra overheads in processing that can be avoided.

Charles

Yea I ran into the same problem when I was trying to put dates into the DB.
I think you might be able to change the field structure type to Text and that might work.
Been awhile since I messed with it.

xyz

Yeah, thought about using a text field type, but then you loose the ability to sort or compare properly in your SQL query.

I'm sure there must be some way to change the default date. I'll keep on the search and post an solutions I find in this thread.

Meanwhile, if anybody else has any ideas, I'd appreciate them.

Thanks.

Charles

you can't sort a text type field? I don't see why not.
You could use 2007-01-12 (Year-Month-Day) and sort it out by the year then the month they the day.
2007-10-31
2007-12-31
2008-01-15
2008-02-23
2008-08-03

You can still order those by Assc or Desc and it would be in the correct order.

Terra

DATETIME is what it is - you can't change it. But you can format the output when you pull the data from the DB.

DATETIME info: MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 10.3 Date and Time Types

few commands I found useful: MySQL AB :: MySQL 3.23, 4.0, 4.1 Reference Manual :: 11.6 Date and Time Functions

all the best - Edith

__________________
Underground, Overground, Wombling Free!

xyz

Thanks Charles, Terra.

OK, I agree you can sort it, I was thinking about the UK format, but you can't compare two dates or use between in the SQL function if it's a text field.

Looks like I'll need to keep formatting it in the SQL query.

Thank you all again.