order by date

zzz

One of my table fields contains date "record_date".

I display results and would like to order by "record_date". Currently it displays the fields in the following manner:

01-28-2004
12-29-2003
12-30-2003

What is it looking for, the month or year? Obviously it's not doing it right.

The_Chancer

Depends what the column is set for...

Is it a DATE column / mysql database ?
If so - it should be YYYY - MM - DD

(my personal preference is for timestamp - very easy to order...)

zzz

It's not a stamp. It is a db field, but the type is: varchar(15). I can not use the stamp as I pass this value several times from one field to the other and some times days later, so I need to keep that innitial value unchanged.

Say it's just a value that looks like date, how can I order it?

The_Chancer

WHy not store the unix timestamp in the DB then instead of storing it as a full date ?

Anyway - that aside... as to ordering it, it is now fairly difficult. You can strtotime() and place in an array then order the array, providing the keys in the right order, which you can then use to look back at the database.

However I would (unless you are too far into the project) look at changing the DB to a DATE column - which MySQL can order very quickly - or saving your dates as a timestamp in the same varchar column, which can then be ordered (and this may be irrelavent) to the second.

zzz

It's too far into development. As a matter of fact it's just a maintenence of the existing stuff.

What does strtotime() do? Does it interpret a string as time stamp? Than sounds like something that may work. How could I implement?

Xoid

You could use the SQL function DATE_FORMAT to order the date according to the format, e.g:

just add ORDER BY DATE_FORMAT(<datefield>,'%d %m %Y') DESC

assuming you store the dates as 01-28-2004 i.e. month-day-year

just use: ORDER BY DATE_FORMAT(<datefield>,'%m %d %Y') DESC

so this statement should give you output, such as:

01-28-2004
12-30-2003
12-29-2003

Specifiers used:

%d Day of the month (00..31)

%m Month (01..12)

%Y Year 4 digits (e.g. 2004)

Hope this helps