We help IT Professionals succeed at work.

How to select records with dates with multiple 2 weeks date from today?

Medium Priority
333 Views
Last Modified: 2012-05-11
Hi, I need to generate a list every 2weeks for records that DateOfFirstVisit is in multiple of 2 weeks from today's date.  so, DateOfFirstVisit date part only, no time part, could be 2, 4, 6,. 8, 10, 12.... weeks from today's date.  How can I do that?  My current query is not working.  Thank you.
select StudyId,accountKey,emailAddress from dbo.Patients
	where DateAdd(wk,Convert(date, DateOfFirstVisit), 2) = CONVERT (date, GETDATE())

Open in new window

Comment
Watch Question

Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
CERTIFIED EXPERT

Commented:
select StudyId,accountKey,emailAddress from dbo.Patients
        where DateAdd(week,2, Convert(date, DateOfFirstVisit)) = CONVERT (date, GETDATE())
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Sam, got the following error from your query.   Thank you
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 1
Type date is not a defined system type.

Hi P, got similar error from your query too. Also, I need to get those of multiple of 2 weeks like what sam has.  Thanks.
Msg 243, Level 16, State 1, Line 4
Type date is not a defined system type.
Msg 243, Level 16, State 1, Line 4
Type date is not a defined system type.

Author

Commented:
Hi Sam, Ok, apparently sql 2005 doesn't support Date but only DateTime.  How to fix this query so it will work in sql 2005?  Thank you.
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT

Commented:
"t" in the above is your "DateOfFirstVisit"
Brendt HessSenior DBA
CERTIFIED EXPERT
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION
Hamed NasrRetired IT Professional
CERTIFIED EXPERT

Commented:
Vheck with this:
table:a
datefield: dd

SELECT a.f1, Int(DateDiff("d",[dd],Now())/14) AS ddif
FROM a;

Author

Commented:
Thanks everyone.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.