views:

183

answers:

3

My content table looks like (contentID, title, created). I need to get all content that was created more than 3 weeks ago.

Sql server database, created is datetime type.

+2  A: 

in MS SQl 2000/2005 you can do this

Select
   contactID,
   title,
   created
from
   content
where
   created < getdate()-21
J.Hendrix
You should try to stay away from implicit "dateadd(day)" math. Why? The new DATE data types in SQL Server 2008 will not like the + or - operators (there will be a type clash error). I used GETDATE()+-INT for a long time and am treaking to break the habit myself.
Aaron Bertrand
+10  A: 

How about:

select contentID, title, created 
from content
where created < dateadd(week,-3,getdate());

This sticks closer to the question. 21 days is fine, obviously means the same, but I find that it's good to use the terminology used in the question.

For example... a while back I was asked to survey an average of 1 in 50 visitors to a site. I described this as a proportion of 0.02, and the client wasn't happy. I pointed out to the client that they're the same, but I learned my lesson, and now if I change the way that something is described, I make sure I comment to that effect, and preferably don't change it in the first place. If the client wants 3 weeks, do it as 3 weeks, not 21 days.

Rob Farley
+1 Good argument
J.Hendrix
+1, yeah, good point
Rubens Farias
+1 And when they want to change it to 4 weeks, they don't want to do the math (I guess that's why we have computers and programmers.).
Jeff O
A: 

Try this:

select contentID, title, created from content
where created < dateadd(day,-21,getdate())
Himadri