Jump to content

order by date from today, and ignore year


Go to solution Solved by spiderwell,

Recommended Posts

Hi all

 

I have a datetime field on my database table, and the rows have dates of events over the last hundred years, and I want to somehow order by month and day from todays date but ignore the year, so i guess that is a group by daymonth type thing.

 

the idea being when you hit the page, you will see anything that happend on this day but in any previous years too, and then work backwards in day/months.

 

so say i had 3 dates,

 

a)1st april 2013

b)1 march 2005

c)1st april 2000

 

the desired order would be a,c,b , if the query was run on 1st april, but if it was run on 1st march the order would be b,a,c.

 

I hope that makes sense.

 

I tried this:

 

GROUP BY DAYOFMONTH(item_date) ORDER BY `item_date` DESC

which gets the order correct, but i then need it to associate with current date, so i tried this but it just threw me an empty record set:

 

WHERE  DATE(item_date) = DATE(NOW()) GROUP BY DAYOFMONTH(item_date) ORDER BY `item_date` DESC

 

anyone have any pointers or ideas?

ok still working on this, I am now at this point, were i want to just have a week span from today, so 15march back to 8th march, but any year

 

WHERE DAYOFYEAR(item_date) <= DAYOFYEAR(NOW()) AND DAYOFYEAR(item_date) >= DAYOFYEAR(NOW()-7) ORDER BY MONTH(item_date), DAY(item_date) DESC

 

it works with 1 WHERE parameter, i.e. less than today, but not with both, I also tried using BETWEEN instead of <=  >= and it made no difference.

 

thanks


$end = date('m-d');
$start = date('m-d', strtotime("-7 days"));
$sql = "SELECT * FROM events
WHERE DATE_FORMAT(thedate, '%m-%d') BETWEEN '$start' AND '$end'
ORDER BY DATE_FORMAT(thedate, '%m-%d') DESC";
This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.