CODEWITHSUNDEEP
phpmysql
Wanye
Wanye

Reputation: 485

Sort SQL result by date greater than today (d-M-Y)

In my sql query I output dates in chronological order.

The dates in my database are stored in d-M-Y format.

What I want to do is sort the results by dates equal to or greater than today to be output first.

In my query I have this sort in my query

...From $db ORDER BY STR_TO_DATE(sortdate, '%d-%M-%Y') ASC

Can anyone tell me if I can do a comparison on todays date as each record is output from the db?

This will give me todays date 

$todaysdate = date("d-M-Y", time());

but can anyone tell me if I can build that into my query?

Thanks in advance.

Upvotes: 1

Views: 3642

Answers (6)

Sundar G
Sundar G

Reputation: 1069

Just put this condition in where like date_column >= curdate()/$todaysdate

thanks

Upvotes: 0

sel
sel

Reputation: 4957

Assuming sortdate is datetime field, in order to display dates equal to or greater than today first,could use UNION.

SELECT * FROM my_table WHERE sortdate>= CURDATE()
UNION
SELECT * FROM my_table WHERE sortdate< CURDATE()

Upvotes: 1

Peter
Peter

Reputation: 1798

My guess is that you saved the date in a VARCHAR column. Please don't do that, you make it very complicated for yourself when you want to do stuff (like this) with the date. I'd suggest that you convert the column to a DATE field and then just use:

SELECT * FROM my_table WHERE my_date_field >= CURDATE()

And if you want to output the date in the d-m-Y format, you can use DATE_FORMAT()

Upvotes: 1

Mina Abadir
Mina Abadir

Reputation: 2981

You can use WHERE sortdate >= $todaysdate

Upvotes: 0

Fluffeh
Fluffeh

Reputation: 33512

You really should be storing the dates in a dateTime format. That will make it much easier to do all sorts of orders, comparisons and plenty of other things. You could for example, then use the mysql now() function to only get the results you need?

...From $db where sortDate>=now() ORDER BYsortdate ASC

Upvotes: 1

TheHe
TheHe

Reputation: 2972

check mysql DATEDIFF in combination with CURRENT_DATE ==> http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_current-date

Upvotes: 2

Related Questions