0

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.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Anders
  • 12,088
  • 34
  • 98
  • 146

8 Answers8

4

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

Gordon Bell
  • 13,337
  • 3
  • 45
  • 64
  • I get an arithmetic overflow error when converting my date column (string, in the format of mmddyyyy) to timestamp. is there a function i can call such as a CType(date,datetime)? – Anders Feb 09 '09 at 21:41
  • Try: convert(datetime, substring(date, 5, 4) + '-' + substring(date, 1, 2) + '-' + substring(date, 3, 2)) – Gordon Bell Feb 09 '09 at 23:20
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

Troy
  • 1,640
  • 10
  • 16
2
DECLARE @start_date datetime
SET @start_date = DATEADD(day, -31, getdate())
SELECT * FROM events WHERE date BETWEEN @start_date AND getdate()
Dave Swersky
  • 34,502
  • 9
  • 78
  • 118
1

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}

dkretz
  • 37,399
  • 13
  • 80
  • 138
0

DateDiff and GetDate() should sort you out.

Example:

SELECT * FROM events WHERE DATEDIFF(day, date, GETDATE()) <= 31 ORDER BY date ASC
Harper Shelby
  • 16,475
  • 2
  • 44
  • 51
Steven Robbins
  • 26,441
  • 7
  • 76
  • 90
0

You could probably use the DATEDIFF function.

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

Wayne Molina
  • 19,158
  • 26
  • 98
  • 163
0

Also:

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

Although really BETWEEN is an abomination

David
  • 3,177
  • 1
  • 18
  • 15
0

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()
JosephStyons
  • 57,317
  • 63
  • 160
  • 234