LinuxQuestions.org
Latest LQ Deal: Latest LQ Deals
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 08-28-2003, 02:31 AM   #1
vous
Member
 
Registered: Mar 2003
Location: Macondo
Distribution: Mandrake 9.1, 10.1, SuSE 8.1 pro, 10.1, Red Hat 8.0/9.0
Posts: 380

Rep: Reputation: 30
Changing date format in MySQL table


Hello All,

I have a Bugzilla installation that uses MySQL DB (of course). The date tables in MySQL currently have the following format:

mm-dd-yyyy

We would like to change it to:

dd/mm/yyyy

Would anybody know a quick hack for this?
 
Old 08-28-2003, 02:42 AM   #2
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
You can always change the format in your query like this:

SELECT DATE_FORMAT(thedatetime,'%d/%m/%y %H:%i:%s') FROM ...

I don't know if it's possible to change the format mysql uses by default.
 
Old 08-28-2003, 02:58 AM   #3
vous
Member
 
Registered: Mar 2003
Location: Macondo
Distribution: Mandrake 9.1, 10.1, SuSE 8.1 pro, 10.1, Red Hat 8.0/9.0
Posts: 380

Original Poster
Rep: Reputation: 30
Does MySQL have a set format or do you set it when you create the table?

By the way, does MySQL have a history file or log file somewhere where I can check the sql statements to see how that table was created?

Last edited by vous; 08-28-2003 at 02:59 AM.
 
Old 08-28-2003, 03:13 AM   #4
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
I'm guessing that you can't change the internal MySQL date format because if you do, all the date functions will return wrong results. But I'm not sure about that.

I always create my tables by using a script file so that I still know what I've done later on. If you don't have such a script, you can always do some reverse engineering to generate one. There are several tools to do this, check out http://www.datanamic.com/ to see if they have any tools to help you out.
 
Old 08-28-2003, 03:19 AM   #5
vous
Member
 
Registered: Mar 2003
Location: Macondo
Distribution: Mandrake 9.1, 10.1, SuSE 8.1 pro, 10.1, Red Hat 8.0/9.0
Posts: 380

Original Poster
Rep: Reputation: 30
On the logging question, I have read in MySQL docs that indeed it does have a query log file, BUT it is not default and you have to flag it on start in order for it to work :-( ...but again any ideas are welcome :-)

On the reverse engineering, even though it sounds cool, we are a bit strapped for time, but if we get an extension I'll definitley check it out....this is not the only issue we friction with.

I'll keep on looking to see what I can find on changing the date format in the table itself....



Last edited by vous; 08-28-2003 at 03:22 AM.
 
Old 08-28-2003, 03:39 AM   #6
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
Good luck, and let me know if you find anything!
 
Old 08-28-2003, 04:42 AM   #7
vous
Member
 
Registered: Mar 2003
Location: Macondo
Distribution: Mandrake 9.1, 10.1, SuSE 8.1 pro, 10.1, Red Hat 8.0/9.0
Posts: 380

Original Poster
Rep: Reputation: 30
Thanks and I will!
 
Old 09-03-2003, 05:40 AM   #8
buttersoft
Member
 
Registered: Aug 2003
Distribution: Slackware
Posts: 178

Rep: Reputation: 30
I am using the excellent EMS MySQL Manager here in the field management you can easily change the date format no problems.

http://www.ems-hitech.com/index.phtml

Brilliant software.

Also according to previous posting above:-

SELECT DATE_FORMAT(dated,'%d/%m/%y') FROM info;

This works fine on my system.




Last edited by buttersoft; 09-03-2003 at 05:45 AM.
 
Old 09-03-2003, 06:30 AM   #9
nephilim
Member
 
Registered: Aug 2003
Location: Belgium
Distribution: Debian (server), Kubuntu (desktop)
Posts: 248

Rep: Reputation: 30
Quote:
I am using the excellent EMS MySQL Manager here in the field management you can easily change the date format no problems.
Although this software changes the date format at presentation level, the format in the database stays the same.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Changing System Date Format to DD/MM/YY On RHEL3? krishvij Linux - Newbie 2 03-16-2005 06:58 AM
Date format Mik LQ Suggestions & Feedback 8 12-05-2004 10:14 AM
MySQL date format mnauta Linux - General 2 05-13-2004 06:20 PM
MySQL non-realtime table-by-table mirroring Passive Linux - Software 1 01-20-2004 12:11 PM
How to import MS ACCESS Table including OLE filed into the MySQL Table ? myunicom Linux - General 1 11-28-2003 11:30 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 05:37 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration