[SOLVED] ordering dates that are closest to the current date

[tallberg]

Hi

Im trying to select events that coinced with a date. The date must be no more than 3 month aheaut. -- this is all working

 

Lastly i want it to order events whose eve_start_date are closest to the current date.

 

Ive tryed order by curdate() but that seems to make an approximation of the between dates.

 

This is what i got.

 

SELECT * FROM event WHERE date( "2008/2/8" ) BETWEEN eve_start_date AND eve_end_date AND eve_confirmed = 1 AND eve_start_date <= DATE_ADD( CURDATE() , INTERVAL + 3 MONTH ) ORDER BY eve_start_date

 

Thanks if anyone can help

[fenway]

I'm not sure I understand why you're wrapping your date in DATE().

 

And I don't know what you mean by "approxmation".

[tallberg]

I believe

 

SELECT * FROM event WHERE date( "2008/2/8" ) BETWEEN eve_start_date AND eve_end_date

 

retieves records that have dates between this date.

 

"Approximation". What i mean is that "order by curdate()"  orders the records whose  eve_start_date AND  eve_end_date are closest to the current date not just the eve_start_date.

[fenway]

I believe

 

SELECT * FROM event WHERE date( "2008/2/8" ) BETWEEN eve_start_date AND eve_end_date

 

retieves records that have dates between this date.

 

That's not a valid SQL date -- use 2008-02-08 as a string literal, not with the DATE() function.  The latter is for extracting the date component of the DATETIME column.

 

"Approximation". What i mean is that "order by curdate()"  orders the records whose  eve_start_date AND  eve_end_date are closest to the current date not just the .

ORDER BY CURDATE() will do nothing at all... ORDER BY eve_start_date will do as you say, it doesn't know anything about "closest".

[tallberg]

Thank you.

 

Have you got any hunches how i might order the results that have eve_start_date closer to the current date.

[fenway]

You could order by the different in dates with DATEDIFF().

[tallberg]

Not sure what to do. This is new to me.

 

This is what ive tryed but it does nothing.

 

SELECT * FROM `event` ORDER BY DATEDIFF( 'eve_start_date', CURDATE( ) )
LIMIT 0 , 30 

 

any more tips

[PFMaBiSmAd]

Be enclosing 'eve_start_date' in single-quotes it is a literal string not a column name (none of your other queries had that, why start now?) That query is probably resulting in a mysql syntax error.

[fenway]

That's correct -- always try to see the output of expressions in the select list first.

[tallberg]

Ok so now ive tryed:

 

SELECT *

FROM `event`

ORDER BY DATEDIFF( eve_start_date, CURDATE( ) )

 

 

SELECT *

FROM `event`

ORDER BY eve_start_date

 

And both produce the same results

 

[fenway]

And what does DATEDIFF( eve_start_date, CURDATE( ) ) return for each row?

[tallberg]

I think i know where you are going.

 

for this

 

SELECT DATEDIFF( eve_start_date, CURDATE( ) )

FROM `event`

 

I get

 

0

-19

43

-18

-17

-4

-19

89

89

 

so i suppose i need to be able to remove the minus signs so the number of days in the past and future are treated the same?

[fenway]

Wrap it in ABS().

[tallberg]

That must be it then

 

SELECT *

FROM `event`

ORDER BY ABS( DATEDIFF( curdate( ) , eve_start_date ) )

 

 

Thank you.