SQL select by date?

Adam

I have a page that prints different results from a table based on criteria specified in the previous page. On of the options is selecting results where they are due in 2 weeks, so I need syntax to select all where date is less than or equal to two weeks from whatever the current date happens to be. So the syntax will look like this:

[PHP]if($v==2){

$SQL="SELECT * FROM tbl_renewal INNER JOIN tbl_customer ON
rnwl_customer_id=customer_id INNER JOIN tbl_renewal_status ON rnwl_status_id=rs_id WHERE
rnwl_customer_id={$_SESSION} AND rnwl_firm_id={$_SESSION} AND
rnwl_date<=(ENTER SYNTAX HERE)";[/PHP]

Any help? Cheers

cgarvey

If it's a recent MySQL, then something like

DATE_ADD( NOW(), INTERVAL 14 DAY )

(yes, that's day singular) should work.. check out the date functions section of the MySQL manual for more.

Adam

Yeah that looks good, but I just realised I need to make it a BETWEEN statement dont i? if i use rnwl_date<=DATE_ADD( NOW(), INTERVAL 14 DAY ) it gives me everything before two weeks from now...where as I only want results between NOW() and two weeks from now... would rnwl_date BETWEEN( NOW () AND INTERVAL 14 DAY) work?

Adam

Never mind, I got it:

SELECT * FROM tbl_renewal WHERE rnwl_date>CURDATE() AND rnwl_date<=DATE_ADD( NOW(), INTERVAL 14 DAY )

Cheers cgarvey, you put me on the right track!

Peace

I would tend to use the BETWEEN operator for that sort of thing.

Adam

True, when I tried it first with the BETWEEN i had the syntax terribly wrong and assumed it didnt work, i've fixed that now.