Mysql Date Format
Results 1 to 10 of 10

Thread: Mysql Date Format

  1. #1
    Senior Member
    Join Date
    Apr 2004
    Posts
    228

    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.
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  2. #2
    Just a Virtualized Geek MrLinus's Avatar
    Join Date
    Sep 2001
    Location
    Redondo Beach, CA
    Posts
    7,324
    Goodbye, Mittens (1992-2008). My pillow will be cold without your purring beside my head
    Extra! Extra! Get your FREE copy of Insight Newsletter||MsMittens' HomePage

  3. #3
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    Quote Originally Posted by MsMittens
    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
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  4. #4
    Super Moderator: GMT Zone nihil's Avatar
    Join Date
    Jul 2003
    Location
    United Kingdom: Bridlington
    Posts
    17,190
    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/
    If you cannot do someone any good: don't do them any harm....
    As long as you did this to one of these, the least of my little ones............you did it unto Me.
    What profiteth a man if he gains the entire World at the expense of his immortal soul?

  5. #5
    Senior Member therenegade's Avatar
    Join Date
    Apr 2003
    Posts
    400
    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);

  6. #6
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    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
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  7. #7
    Super Moderator: GMT Zone nihil's Avatar
    Join Date
    Jul 2003
    Location
    United Kingdom: Bridlington
    Posts
    17,190
    Hmmm,

    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?
    If you cannot do someone any good: don't do them any harm....
    As long as you did this to one of these, the least of my little ones............you did it unto Me.
    What profiteth a man if he gains the entire World at the expense of his immortal soul?

  8. #8
    Senior Member
    Join Date
    Apr 2004
    Posts
    228
    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 .
    Don\'t post if you\'ve got nothing constructive to say. Flooding is annoying

  9. #9
    Member omin's Avatar
    Join Date
    Feb 2002
    Posts
    60
    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
    \"Clouds are not spheres, mountains are not cones, coastlines are not circles, and bark is not smooth,
    nor does lightning travel in a straight line.\" -Benoit Mandelbrot

  10. #10
    Senior Member
    Join Date
    Oct 2003
    Location
    MA
    Posts
    1,053
    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 :-)

Similar Threads

  1. Windows Error Messages
    By cheyenne1212 in forum Miscellaneous Security Discussions
    Replies: 7
    Last Post: February 1st, 2012, 02:51 PM
  2. Apache, PHP, MySQL with basic security settings.
    By nightcat in forum The Security Tutorials Forum
    Replies: 9
    Last Post: May 28th, 2005, 03:47 AM
  3. Debate about Data Recovery after Format.
    By helloworid in forum Newbie Security Questions
    Replies: 10
    Last Post: April 30th, 2004, 09:27 PM
  4. Tcp/ip
    By gore in forum Newbie Security Questions
    Replies: 11
    Last Post: December 29th, 2003, 08:01 AM
  5. Security Policy
    By instronics in forum The Security Tutorials Forum
    Replies: 7
    Last Post: February 5th, 2003, 10:04 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •