How to do conditional SQL Server statements?

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.

Answers


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


Use DateDiff:

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

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


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

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}


You could probably use the DATEDIFF function.

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


Also:

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

Although really BETWEEN is an abomination


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()

DateDiff and GetDate() should sort you out.

Example:

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

Need Your Help

Nimiety text with inserted data

c# sql-server wpf

I am trying to insert data in database (WPF). This is my connection code:

wxPython UltimateListCtrl check (tick) listitem programatically

python checkbox wxpython

I am using UltimateListCtrl with LC_LIST style. I have all items in list with checkboxes. After populating list, I am trying to check (tick) some items dynamically as per the data I receive from

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.