How to do conditional SQL Server statements?

Question

I have a database with event information, including date (in MMDDYYYY format). is it possible to write an SQL Server statement to only get rows that fall within a certain time frame?

something like this pseudo-statement:

SELECT * FROM events WHERE [current_date minus date <= 31] ORDER BY date ASC

where date is the date in the SQL Server row and current_date is today's date. The 31 is days, so basically a month's time.

I can easily process the data after a generic statement (read: SELECT * FROM events ORDER BY date ASC), but it would be "cool" (as in me learning something new :P) to know if this is possible.

Answer 1

DateDiff and GetDate() should sort you out.

Example:

SELECT * FROM events WHERE DATEDIFF(day, date, GETDATE()) <= 31 ORDER BY date ASC

Answer 2

Use DateDiff:

SELECT * FROM events WHERE DATEDIFF(day, date, getdate()) < 31 ORDER BY date ASC

http://msdn.microsoft.com/en-us/library/ms189794.aspx

Answer 3

SELECT * FROM events WHERE date > getdate() - 31 ORDER BY date ASC

Answer 4

You could probably use the DATEDIFF function.

D'oh, Ninja'd while looking it up to make sure I wasn't speaking nonsense! :)

Answer 5

Also:

SELECT * 
FROM events 
WHERE current_date BETWEEN dateadd(day,-31,getdate()) AND getdate() 
ORDER BY date ASC

Although really BETWEEN is an abomination

Answer 6

Use GetDate() to return the current date.

Add or subtract in days.

An example would be:

SELECT *
FROM MY_TABLE T
WHERE T.SOME_DATE BETWEEN (GetDate()-31) AND GetDate()

Answer 7

DECLARE @start_date datetime
SET @start_date = DATEADD(day, -31, getdate())
SELECT * FROM events WHERE date BETWEEN @start_date AND getdate()

Answer 8

For best performance, don't use a function in the expression. First calculate the threshold date (in your client or a stored procedure) and then use

SELECT ... WHERE [date] > {put threshold here}