Jump to content

Archived

This topic is now archived and is closed to further replies.

greggustin

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

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)

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
Share on other sites
you can use the strtotime function to convert the datetime string into a unix timestamp

Share this post


Link to post
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

Share this post


Link to post
Share on other sites

×

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.