Jump to content


how do you order by date if dafe field is a text field?

  • Please log in to reply
6 replies to this topic

#1 greggustin

  • Members
  • PipPip
  • Member
  • 22 posts

Posted 19 July 2006 - 08:28 PM

I do not like the forced format of dates
(can this be changed?)

so I made the date field = varchar
I am the only "forms entry person" for our club
and we print out the dues monthly on a php web page
in reverse order (of date)

10 membername 1 jul 18
11 membername 2 jul 17
15 membername 3 jul 12
12 membername 4 jul 04

ergo = I enter the 'date' field in my html forms as jul 4, etc
but the 'order by' does not work as desired


when I did a sample ["order by" paid] (ie $)
it listed them as desired (ie my syntax is ok - just not for the varchar field for date)

#2 pixy

  • Members
  • PipPipPip
  • Advanced Member
  • 295 posts

Posted 19 July 2006 - 08:30 PM

You should use the DATETIME or DATE column type for dates. Then, when you take them out of the database use the DATE_FORMAT() function to make them look prettier.

I dont know how you would sort through them if it's varchar or other text type.

This is a .44 Caliber Loveletter straight through my heart.

Tabulas + Threadless + Hire Me!

#3 greggustin

  • Members
  • PipPip
  • Member
  • 22 posts

Posted 19 July 2006 - 10:27 PM

my further research leads me to belive you cannot ORDER on varchar
ok - will research the date_format stuff
but am I STUCK with the input of xxxx-xx-xx?
duh - these are all 2006 !

ws thinking if inputing the month and day in differnet fieilds :)
then sort on day ! :P

#4 robos99

  • Members
  • PipPipPip
  • Advanced Member
  • 55 posts

Posted 19 July 2006 - 10:32 PM

I beleive that attempting to order by a text field will result it in being returned in alpahbetical order, which is of no use for dates. Your best bet is to either use a datetime or date column type, or use an interger column type and use unix timestamps. You complained about the forced format of the dates....with a unix timestamp you could really just format it for anything you like, but you can do that with the datetime and time column types as well.

#5 AndyB

  • Staff Alumni
  • Advanced Member
  • 5,465 posts
  • LocationToronto

Posted 19 July 2006 - 10:44 PM

If you want to do anything with dates, then you must exert control over the form/format with which dates are entered and not leave it up to your visitors - July 19, 19 July, 19th July, etc. etc. are all the same to people and impossible to use in a database application.

Expecting your visitors to enter information in a pre-selected format is best (or only) accomplished by forcing them to adopt something they may find awkward.  A simple solution is to use a 'date picker' script that allows them to choose a date they recognize but which generates a date in a controlled format that you can use for a database.  http://www.rainfores...etimepicker.htm is one I've used in a number of commercial sites and seems to work well.

Just because the date is stored in your database as yyyy-mm-dd (the only sortable form), doesn't mean you have to display it to site visitors that way.  The date() and mktime() functions allow you to re-present a yyyy-mm-dd date in whatever way you want.
Legend has it that reading the manual never killed anyone.
My site

#6 ryanlwh

  • Staff Alumni
  • Advanced Member
  • 511 posts

Posted 20 July 2006 - 12:14 AM

you can use the strtotime function to convert the datetime string into a unix timestamp
Please use EDIT * 100...
Please use
or [php] * 1000...


#7 hitman6003

  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 20 July 2006 - 12:36 AM

SELECT * FROM database ORDER BY str_to_date(datefieldname, '%e %b %y');

so long as your date format will always be 'DD MMM YY' with no leading zeros for the days.  The full list of date formats is in the MySQL manual:


0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users