Page 1 of 1

Sorting by Date

Posted: Sun Jul 27, 2003 6:19 pm
by okee
I have a few rows with the following dates

28/07/2003
25/07/2003
24/07/2003

now if i add the date 27/06/2003 and i do a query with sort by date it returns.

28/07/2003
27/06/2003
25/07/2003
24/07/2003

Is there any way to sort it correctly or do i need to use a different date format ?

okee

...

Posted: Sun Jul 27, 2003 7:50 pm
by kettle_drum
Yeah i would just recomend storing all date values as the unix time value as it makes anything you want to do with dates easier.

Posted: Mon Jul 28, 2003 3:54 am
by twigletmac
Or use the DATE field type of your database - never store dates in VARCHAR fields.

Mac

Posted: Mon Jul 28, 2003 12:48 pm
by SBukoski
twigletmac wrote:Or use the DATE field type of your database - never store dates in VARCHAR fields.

Mac
Why? If I'm going to be doing a lot of manipulation, then sure, I definitely don't want to use a Varchar format as it makes it slightly more difficult. But if my only purpose is to display a date, then I don't see a problem. it's actually one less step as you don't have to convert the timestamp to a string representation.

Also, I store any date I want to store in the format YYYY-MM-DD. this gauratees that they will sort properly.

Is there some major, underlying reason you think storing dates as VARCHAr is bad?

Posted: Mon Jul 28, 2003 6:14 pm
by sitething
Pardon me for butting in, eh! :D But as someone who's been bitten in the ass by this before, I just can't resist.

You've already shown that storing as a varchar does NOT sort properly... at least not without a bit of mojo.

For me, just as a matter of personal preference, if I'm storing a date then I store it as some kind of time-referenced data type. Like a timestamp. Because see you never know what you'll want in the future. Maybe you don't need to do anything other than list the dates now, but someday you may, and when that day comes and you have all these varchars to deal with... you'll regret it. :? I know I did.

Best thing is, make your data type correct and you can't go wrong. But it's up to you really :wink: