search date, present to defined number of days ago

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

search date, present to defined number of days ago

Author

Topic

hat-rack
Starting Member

1 Post

Posted - 2004-07-09 : 06:10:10

Im trying to search between two dates, from the present to 2 weeks ago, 2 days ago, a month ago, what ever the value in a dropdown box says.

Im a noob and I am having trouble defining "present" and "2 days ago" or "1 week ago" in a way that SQL will understand. my code at the moment looks like this and works (note there is no date functions there):

SELECT *
FROM Candidates
WHERE candLocation Like 'varoffice' AND candDept Like 'vardept' AND candTypeRole Like 'varrole' AND (candName Like '%varSearch%' OR candEmail Like '%varSearch%')
ORDER BY candDate DESC

to add the date it would look something like this im guessing:

SELECT *
FROM Candidates
WHERE candLocation Like 'varoffice' AND candDept Like 'vardept' AND candTypeRole Like 'varrole' AND (candDate BETWEEN 'varPresentDate' AND 'varLaterDate') AND (candName Like '%varSearch%' OR candEmail Like '%varSearch%')
ORDER BY candDate DESC

Im using an access DB and I understand that there is type mismatch issues with the date you have to keep your eye out for.

How do I define the present date in SQL and then define a date 2, 8, 10, 50 or any number of days in the past?

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2004-07-09 : 06:15:38

For the current date in SQL Server with no time portion:-

select dateadd(dd,datediff(dd,0,getdate()),0)

to subtract days:-

select dateadd(dd,-1,getdate())

In Access, just replace getdate() with Now(), you will also need to put quotes around the "dd"...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources