Jump to content

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


greggustin

Recommended Posts

I do not like the forced format of dates
xxxx-xx-xx
(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)

eg
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

hints?

ps
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)
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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.rainforestnet.com/datetimepicker.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.
Link to comment
Share on other sites

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

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:

http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html#id3085964
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.