select records within a date range

[otuatail]

Hi this is a dificult one for me. I want to select records from the last 3 days. ie

select * from table where Date > '2010-05-24 00:00:00' and Date < '2010-05-27 23:59:59'

 

I can get todays date and add 23:59:59. The problem is subtracting three days. is there any mysql function that can be used for the first date (today - 3 days)

 

Desmond.

 

[otuatail]

Ok I think I have nearly solved this.

This works

SELECT * 
FROM WorldCup
WHERE Date > DATE_ADD( CURDATE( ) , INTERVAL 15 
DAY ) 
AND Date < DATE_ADD( CURDATE( ) , INTERVAL 16 
DAY ) 

This dosn't

SELECT * 
FROM WorldCup
WHERE Date > DATE_ADD( CURDATE( ) +'00:00:00', INTERVAL 15 
DAY ) 
AND Date < DATE_ADD( CURDATE( ) + '23:59:59', INTERVAL 15 
DAY ) 

How do I get the hours into it as it will default to midnight.

[Maeltar]

You could try ..

 

DAY(CURDATE()-3)

 

also works with

 

WEEK(CURDATE()-3)

MONTH(CURDATE()-3)

YEAR(CURDATE()-3)

 

Obviously change the "-3" to suit

[ignace]

WHERE Date BETWEEN '2010-05-24' AND '2010-05-27'

[fenway]

You could try ..

 

DAY(CURDATE()-3)

 

also works with

 

WEEK(CURDATE()-3)

MONTH(CURDATE()-3)

YEAR(CURDATE()-3)

 

Obviously change the "-3" to suit

And this won't work anyway -- you need INTERVAL for math.