CODEWITHSUNDEEP
sql-server-2005t-sqldate
Udaya Lakshmi
Udaya Lakshmi

Reputation:

Sql server 2005 date function

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.

Upvotes: 0

Views: 605

Answers (3)

Kim Gräsman
Kim Gräsman

Reputation: 7586

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

Upvotes: 0

Matt Sach
Matt Sach

Reputation: 1170

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

Upvotes: 1

Jason Musgrove
Jason Musgrove

Reputation: 3583

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;

Upvotes: 6

Related Questions