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

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

lapuccaAsked:
Who is Participating?
 
Vadim RappConnect With a Mentor Commented:
where datediff(d,t,getdate()) % 14 =0


In order to calculate datediff in days, it will convert automatically.
0
 
Alpesh PatelAssistant ConsultantCommented:
select StudyId,accountKey,emailAddress from dbo.Patients
        where DateAdd(week,2, Convert(date, DateOfFirstVisit)) = CONVERT (date, GETDATE())
0
 
sameer2010Connect With a Mentor Commented:
Try this. It would give you all appointments which are in multiples of 2 weeks from the current date.
select StudyId,accountKey,emailAddress from dbo.Patients
	where Datediff(DAY,CONVERT(date,getdate()),CONVERT(date, DateOfFirstVisit))%14 = 0

Open in new window

0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
lapuccaAuthor 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.
0
 
lapuccaAuthor 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.
0
 
Vadim RappCommented:
"t" in the above is your "DateOfFirstVisit"
0
 
Brendt HessConnect With a Mentor Senior DBACommented:
This query should do the trick:

select
    StudyId,accountKey,
    emailAddress
from dbo.Patients
where Datediff(day, Convert(varchar(8), DateOfFirstVisit, 112), CONVERT (varchar(8), CURRENT_TIMESTAMP, 112)) % 14 = 0
    AND Dateadd(day, -1, CURRENT_TIMESTAMP) > DateOfFirstVisit

This version is a variation of sameer2010's version.  In that version (as in your original sample code, the CONVERT was to a Date value.  However, the Date datatype is new to SQL2008, so the convert statement would fail in SQL 2005 and earlier.  This version uses the SQL 2005 and earlier syntax, which is still compatible with SQL 2008.

This comparison must be done by day count, since comparisons by week in DateDiff will return a value of two weeks when the days are any day within (for example) week 11 and week 13.  If your current date was in the 13th week of the year, a DateDiff by Week would match any day in the 11th week of the year.

There are a couple of places that might be unclear here.  

CONVERT(varchar(8), <date value>, 112) - this converts the date value to a string in the form 'YYYYMMDD', with no time attached.  When you use these strings in a DATEDIFF function, they are automatically converted to a datetime value with no time attached.

.... AND Dateadd(day, -1, CURRENT_TIMESTAMP) > DateOfFirstVisit - Make sure the DateOfFirstVisit is not the current date.

0
 
hnasrCommented:
Vheck with this:
table:a
datefield: dd

SELECT a.f1, Int(DateDiff("d",[dd],Now())/14) AS ddif
FROM a;
0
 
lapuccaAuthor Commented:
Thanks everyone.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.