6 Replies Latest reply: Mar 21, 2010 4:46 AM by David_Powers RSS

    Interesting issue with MySQL Dates

    Mike_Watt Community Member

      Okay - so... here goes:

       

      I have a table of events that includes a standard date field (2010-03-17).

      The query (partial) is structured this way:

       

      SELECT DATE_FORMAT(`date`, '%m/%d') AS Date

      In SQL, this provides me with 03/17. My problem is that when I then do ORDER BY date, start_time ASC it is only considering the day/month when ordering the results. I need to display only day/month, but I need the YEAR included when figuring the display order.  I just assumed that my query was only affecting the visual representation of the record, not the way the record is calculated.

      Does anyone know how best I can accomplish what I need to?

      Thanks in advance.
      +mf

      www.melsbgc.com

        • 1. Re: Interesting issue with MySQL Dates
          bregent CommunityMVP

          Seems like a bug as you are sorting by the column name, not the alias. You could try adding another column formatted with the full date and sort by its alias. Not sure if that would work.

          • 2. Re: Interesting issue with MySQL Dates
            The_FedEx_Guy Community Member

            would an explode function work?

            • 3. Re: Interesting issue with MySQL Dates
              Mike_Watt Community Member

              I'll give that a shot... pull the full date in the query, and don't display it, just use it for sorting.  The other thing I thought of is calling the full date in the query, and format the display in PHP, instead.  Like:

               

              SELECT `date` FROM events


              Then, in PHP:

               

              <?php echo  date("j/d", strtotime($row_events['date'])) ;?>

               

              I can't test it right now, but I think that would work, no?

              • 4. Re: Interesting issue with MySQL Dates
                David_Powers CommunityMVP

                Mike_Watt wrote:

                 

                SELECT DATE_FORMAT(`date`, '%m/%d') AS Date


                In SQL, this provides me with 03/17. My problem is that when I then do ORDER BY date, start_time ASC it is only considering the day/month when ordering the results.

                SELECT DATE_FORMAT('date', '%m/%d') AS formatted_date
                ORDER BY date, start_time ASC
                
                • 5. Re: Interesting issue with MySQL Dates
                  Mike_Watt Community Member

                  David:

                   

                  Is your point that "AS Date" is the problem, and that if I change it to something else (formatted_date) I should be okay?  I saw several highlighted parts of your query, but most of them match what I'd already done.

                   


                  EDIT: I was playing around with it last night and got it to work with this:

                   

                  SELECT DATE_FORMAT(`date`, '%m/%d') AS Date, DATE_FORMAT(start_time, '%l:%i%p') AS Time, DATE_FORMAT(end_time, '%l:%i%p') AS End, title AS Event, id, date AS f_date

                  FROM events

                  WHERE `date` >= curdate()

                  ORDER BY f_date, start_time ASC

                   

                  But from what I'm getting from your post, David, is that I've basically just created an extra, unnecessary step, is that correct? In this case, if I edited the above SQL to read the following it would be [more] correct(?) :

                   

                  SELECT DATE_FORMAT(`date`, '%m/%d') AS f_date, DATE_FORMAT(start_time, '%l:%i%p') AS Time, DATE_FORMAT(end_time, '%l:%i%p') AS End, title AS Event, id

                  FROM events

                  WHERE f_date >= curdate()

                  ORDER BY date, start_time ASC

                  • 6. Re: Interesting issue with MySQL Dates
                    David_Powers CommunityMVP

                    Mike_Watt wrote:

                     

                    Is your point that "AS Date" is the problem, and that if I change it to something else (formatted_date) I should be okay?  I saw several highlighted parts of your query, but most of them match what I'd already done.

                    The highlighting is simply the way this forum highlights SQL. I think the idea is that keywords are highlighted.

                     

                    Yes, you have got the basic idea. You're creating an alias to hold a different value, so when you reassigned the value of the date column to Date, you ended up with the order being based on the reformatted value. On a Linux server, date and Date would be treated differently, but Windows is case-insensitive.

                     

                    Your final version is fine, except that you should compare date, rather than f_date, with curdate().