Web Hosting Forums

Results 1 to 9 of 9

This is a discussion on MYSQL Query in the Hosting Talk & Chit-chat forum
I'm selecting some data data from a MYSQL database, thing is, it's formatted like this... yyyy-dd-mm hh-mm-ss 2008-02-01 00:00:00 And i want it formatted like.... ...

  1. #1
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118

    MYSQL Query

    I'm selecting some data data from a MYSQL database, thing is, it's formatted like this...

    yyyy-dd-mm hh-mm-ss
    2008-02-01 00:00:00

    And i want it formatted like....

    dd-mm-yyyy
    02-01-2008

    Could somebody give me an example on how i would format the query to do this....

    Thanks

  2. #2
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118
    Hmmm somebody recommended doing something like this...

    select DATE_FORMAT(dodgyFormattedDate,'%d-%m-%Y')

    But my query looks like this....

    SELECT * FROM portal.P_products WHERE release_date<=DATE_ADD(CURDATE(),INTERVA L 30 DAY) AND release_date>=CURDATE()

    How on earth would i tie the query above to what i'm selecting at the moment?

  3. #3
    Loyal Client the_ancient's Avatar
    Join Date
    Feb 2004
    Posts
    3,474
    mysql does all dates in YYYY-MM-DD format, so your "curdate" function is already in YYYY-MM-DD there is no need to convert it.
    -------------------------
    the_ancient
    MP Technology Group

  4. #4
    Ron
    Ron is offline
    Loyal Client
    Join Date
    Aug 2002
    Posts
    7,503
    Quote Originally Posted by beesfan View Post
    Hmmm somebody recommended doing something like this...

    select DATE_FORMAT(dodgyFormattedDate,'%d-%m-%Y')

    But my query looks like this....

    SELECT * FROM portal.P_products WHERE release_date<=DATE_ADD(CURDATE(),INTERVA L 30 DAY) AND release_date>=CURDATE()

    How on earth would i tie the query above to what i'm selecting at the moment?
    You have a few appraoches. One is to list all of the fields in the table portal.P_products.

    Just list them in the same order they appear when you type "desc P_products'" in the user interface, such as

    id, release_date,...

    When you get to dodgyFormattedDate, sneak in the DATE_FORMAT command.

    The problem with this, of course is that some other part of the program might be using dodgyFormattedDate and be depending on the original format.

    Another way is you can ADD it to the select statement and use the new column just for yourself, like:

    SELECT *, DATE_FORMAT(dodgyFormattedDate,'%d-%m-%Y') as MyNewDate from ....

    Have fun!
    Good luck

  5. #5
    Community Leader jason's Avatar
    Join Date
    Sep 2001
    Location
    Rochester, NY
    Posts
    5,884
    You can also easily reformat the date in your script after it is returned from MySQL. This will be a bit more resource intensive than just having MySQL do it itself, but if you're worried about breaking something by changing the query then this is a decent solution.

    If you are using PHP you can do this:
    PHP Code:
    $date date('d-m-Y'strtotime($dateFromSQL)); 
    Or, just using simple string manipulation:
    PHP Code:
    $date substr($dateFromSQL82) . '-' 
            
    substr($dateFromSQL52) . '-' 
            
    substr($dateFromSQL04); 
    --Jason
    Jason Pitoniak
    Interbrite Communications
    www.interbrite.com www.kodiakskorner.com

  6. #6
    Ron
    Ron is offline
    Loyal Client
    Join Date
    Aug 2002
    Posts
    7,503
    For performance, I try very hard to not do things like that in interpreted PHP but allow the MySQL server to do it in compiled C.

    I realize that I may be trading off good program design for the performance.
    Good luck

  7. #7
    Community Leader jason's Avatar
    Join Date
    Sep 2001
    Location
    Rochester, NY
    Posts
    5,884
    Quote Originally Posted by Ron View Post
    For performance, I try very hard to not do things like that in interpreted PHP but allow the MySQL server to do it in compiled C.

    I realize that I may be trading off good program design for the performance.
    I agree, Ron. The less you have to manipulate a value, the better off you are. If I can pull something out of a database pre-formatted then I will. I did, however, want to present an alternative in case beesfan wasn't comfortable manipulating his queries.

    --Jason
    Jason Pitoniak
    Interbrite Communications
    www.interbrite.com www.kodiakskorner.com

  8. #8
    Loyal Client
    Join Date
    Mar 2007
    Posts
    118
    Thanks for your help guys.

    At first i went with formatting the data once it was pulled from the database, but sometimes i'm pulling huge amounts of data from the database, so like you guys have already said, it made more sense to format the data during the query rather than after we had already grabbed the data.

    I did this...

    PHP Code:
    "SELECT *, DATE_FORMAT(release_date, '%e-%b-%Y') AS release_date FROM data_portal.P_products WHERE release_date<=DATE_ADD(CURDATE(),INTERVAL 30 DAY) AND release_date>=CURDATE()" 

  9. #9
    Ron
    Ron is offline
    Loyal Client
    Join Date
    Aug 2002
    Posts
    7,503
    I wouldn't have done it with that exact syntax. First of all, I don't know that it is permissible to have an alias be the same name as another column in the result set. Secondly, it is not allowable to use a column alias in a WHERE clause. Most importantly it may confuse someone in the future or stop working even if it is working now.

    I would suggest that you change the "AS release_date" to something like "AS my_release_date" and go through the code replacing references to release_date to my_release_date.

    EDIT: http://dev.mysql.com/doc/refman/4.1/en/select.html
    MySQL allows duplicate column names. That is, there can be more than one select_expr with the same name. This is an extension to standard SQL. Because MySQL also allows GROUP BY and HAVING to refer to select_expr values, this can result in an ambiguity:

    SELECT 12 AS a, a FROM t GROUP BY a;

    In that statement, both columns have the name a. To ensure that the correct column is used for grouping, use different names for each select_expr.
    Last edited by Ron; 11-01-2007 at 08:42 AM.
    Good luck

Bookmarks

Posting Permissions

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