Jump to content


Photo

ORDER BY problem with dates


  • Please log in to reply
5 replies to this topic

#1 PWD

PWD
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 08 March 2006 - 03:31 AM

What is the best way to format my MySQL query if I want to display my dates in ascending order?
Currently, as written, my query sorts my dates alphabetically, and I want it sorted numerically:

$query = "SELECT DATE_FORMAT(date_date,'%M %D %Y') AS date_date,date_info from imp_dates ORDER BY date_date ASC";

My search of the MySQL website must not be correct as I'm not producing any results about this.

My gratitude ahead of time for your help...


[!--fonto:Geneva--][span style="font-family:Geneva"][!--/fonto--]My Gratitude---[!--fontc--][/span][!--/fontc--]
(My gratitude in advance for helping me learn; so I may one day give as freely as you have here today)
----------
[!--sizeo:5--][span style="font-size:18pt;line-height:100%"][!--/sizeo--][!--fonto:Optima--][span style="font-family:Optima"][!--/fonto--][!--coloro:red--][span style="color:red"][!--/coloro--]PWD[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#2 greycap

greycap
  • Members
  • PipPipPip
  • Advanced Member
  • 31 posts

Posted 08 March 2006 - 03:35 AM

[!--quoteo(post=352727:date=Mar 7 2006, 09:31 PM:name=PWD)--][div class=\'quotetop\']QUOTE(PWD @ Mar 7 2006, 09:31 PM) View Post[/div][div class=\'quotemain\'][!--quotec--]
What is the best way to format my MySQL query if I want to display my dates in ascending order?
Currently, as written, my query sorts my dates alphabetically, and I want it sorted numerically:

$query = "SELECT DATE_FORMAT(date_date,'%M %D %Y') AS date_date,date_info from imp_dates ORDER BY date_date ASC";

My search of the MySQL website must not be correct as I'm not producing any results about this.

My gratitude ahead of time for your help...
[/quote]

It may be that you are hiding the column name with your alias. Your orderby is probably looking at the alias and not the original column.

Try this:

SELECT DATE_FORMAT(date_date,'%M %D %Y') AS date_string,date_info from imp_dates ORDER BY date_date ASC


#3 PWD

PWD
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 08 March 2006 - 03:41 AM

date_date is the literal name of the column. I did try your suggestion to no avail. I also tried to completely remove the alias (AS date_date...) to no avail...

Still searching
[!--fonto:Geneva--][span style="font-family:Geneva"][!--/fonto--]My Gratitude---[!--fontc--][/span][!--/fontc--]
(My gratitude in advance for helping me learn; so I may one day give as freely as you have here today)
----------
[!--sizeo:5--][span style="font-size:18pt;line-height:100%"][!--/sizeo--][!--fonto:Optima--][span style="font-family:Optima"][!--/fonto--][!--coloro:red--][span style="color:red"][!--/coloro--]PWD[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#4 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 08 March 2006 - 04:57 AM

What column type is date_date? Is it a string type or a date type?

#5 PWD

PWD
  • Members
  • PipPip
  • Member
  • 19 posts

Posted 08 March 2006 - 06:04 AM

It is formatted as a DATE
[!--fonto:Geneva--][span style="font-family:Geneva"][!--/fonto--]My Gratitude---[!--fontc--][/span][!--/fontc--]
(My gratitude in advance for helping me learn; so I may one day give as freely as you have here today)
----------
[!--sizeo:5--][span style="font-size:18pt;line-height:100%"][!--/sizeo--][!--fonto:Optima--][span style="font-family:Optima"][!--/fonto--][!--coloro:red--][span style="color:red"][!--/coloro--]PWD[!--sizec--][/span][!--/sizec--][!--colorc--][/span][!--/colorc--][!--fontc--][/span][!--/fontc--]

#6 kenrbnsn

kenrbnsn
  • Staff Alumni
  • Advanced Member
  • 8,235 posts
  • LocationHillsborough, NJ, USA

Posted 08 March 2006 - 02:34 PM

Can you post a sample of the current output and how you want it to be displayed?

Ken




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users