Sorting when DATE_FORMAT() is used

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

one of the times i used the DATE FORMAT function i was then unable to sort by the date?

is this a normal side effect or was there something wrong in my script?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

probably this query does not what you want it to do

select date_format(date, '....') as date order by date

this one does

select date_format(date,'....') as nicedate order by date
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

Post by malcolmboston »

why the.....

...... nicedate?

whats the difference?
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

the first query would sort on the formatted output


say you have some timestamps, you want to date_format so that it shows the day...

select date_format(timestamp,'%a') as timestamp from stuff order by timestamp

now the sorting would be on monday, tuesday, ...

thus saturday comes before wednesday
Steveo31
Forum Contributor
Posts: 416
Joined: Sun Nov 23, 2003 9:05 pm
Location: San Jose CA

Post by Steveo31 »

The nicedate is there as... I guess like a variable where the DATE_FORMAT is stored. That way, someone could do

Code: Select all

DATE_FORMAT( `input_date` , '%c-%e-%y' ) AS rev_date
and then get it back by a

Code: Select all

while($row = mysql_fetch_array($theQuery)){
echo $row['rev_date'];
}
Post Reply