ADO/MS-SQL date comparison

[Eric Lowe]

Hi -

I've dug through a lot of the SQL/date-related threads, yet none seem to address the problem I'm having. I'm trying to write an ASP/ADO Select command to MS-SQL 7 that would compare dates in the DB to the current date minus six months (e.g. I want to show all items posted in the last six months).

I've jury-rigged it for now with:

SELECT * FROM table
WHERE year(date)=" & year(now) & " //Select if this year
OR year(date)=" & year(now)-1 & " //Select if six months ago last year
AND month(date) >=" & month(now)+6 & "
ORDER BY date DESC

where "date" is the table's date column. Obviously this query would cease to work in five months.

I've considered an IF...THEN, but I think that would require pulling the whole DB and then sorting data on the page, which would make the page heavier than just pulling the data I need in the SELECT statement. I could be wrong, though.

Do any of you know of a better way to accomplish this?

[imoet]

Why not add/substract 188 days from date field and compare with getdate() (or vice versa)?

OK OK you'll gonna be 1 day off "track" in 3 out of the 4 years ;-)