greggustin Posted July 19, 2006 Share Posted July 19, 2006 I do not like the forced format of datesxxxx-xx-xx(can this be changed?)so I made the date field = varcharI am the only "forms entry person" for our cluband we print out the dues monthly on a php web pagein reverse order (of date)eg10 membername 1 jul 1811 membername 2 jul 1715 membername 3 jul 1212 membername 4 jul 04ergo = I enter the 'date' field in my html forms as jul 4, etcbut the 'order by' does not work as desiredhints?pswhen 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) Quote Link to comment Share on other sites More sharing options...
pixy Posted July 19, 2006 Share Posted July 19, 2006 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. Quote Link to comment Share on other sites More sharing options...
greggustin Posted July 19, 2006 Author Share Posted July 19, 2006 my further research leads me to belive you cannot ORDER on varcharok - will research the date_format stuffbut 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 Quote Link to comment Share on other sites More sharing options...
robos99 Posted July 19, 2006 Share Posted July 19, 2006 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. Quote Link to comment Share on other sites More sharing options...
AndyB Posted July 19, 2006 Share Posted July 19, 2006 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. Quote Link to comment Share on other sites More sharing options...
ryanlwh Posted July 20, 2006 Share Posted July 20, 2006 you can use the strtotime function to convert the datetime string into a unix timestamp Quote Link to comment Share on other sites More sharing options...
hitman6003 Posted July 20, 2006 Share Posted July 20, 2006 [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 Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.