Sql server 2005 date function

Question

I would like to know how to get no. of days using getdate() & existing dates in other table, where that table contains multiple records, ie i would like to know the jobs posted 5 days back or 10 days back or 15 days back. I have written the query like

declare @q1 datetime;
select cdate from jobposting where cdate like (select cdate)
select datediff ( dd, q1, getdate())

where q1 must store all the dates in jobposting table, but i cannot get correct answer.

Or i also tried like

select datediff ( dd, select cdate from jobposting, getdate())

thanking U in Advance.

Answer 1

Your question isn't entirely clear, but this should return all jobposting rows where cdate is 5 days old or younger older.

SELECT *
FROM jobposting
WHERE DATEDIFF(dd, cdate, GETDATE()) >= 5

Answer 2

If I understand your question correctly, there are two common ways to achieve what you request:

First, using datediff() as Kim suggests.

The other is to work out what the date is 5 days ago, and then compare the dates in the table to it (which should be faster than DateDiff if the column is indexed):

declare @cutoff datetime;
set @cutoff = dateadd(day, -5, getdate());
select * from jobposting where cdate >= @cutoff;

Answer 3

This should do it, assuming 5 days. @days could be asked for as a parameter to a stored procedure to make it flexible:

DECLARE @days int
SET @days = 5

SELECT cdate
FROM jobposting
WHERE datediff(d, cdate, getdate()) <= @days