?
Solved

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

Posted on 2011-04-19
11
Medium Priority
?
291 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

0
Comment
Question by:lapucca
9 Comments
 
LVL 21

Expert Comment

by:Alpesh Patel
ID: 35430393
select StudyId,accountKey,emailAddress from dbo.Patients
        where DateAdd(week,2, Convert(date, DateOfFirstVisit)) = CONVERT (date, GETDATE())
0
 
LVL 13

Assisted Solution

by:sameer2010
sameer2010 earned 668 total points
ID: 35430721
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
 

Author Comment

by:lapucca
ID: 35434327
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:lapucca
ID: 35455142
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
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 668 total points
ID: 35476583
where datediff(d,t,getdate()) % 14 =0


In order to calculate datediff in days, it will convert automatically.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 35476592
"t" in the above is your "DateOfFirstVisit"
0
 
LVL 32

Assisted Solution

by:Brendt Hess
Brendt Hess earned 664 total points
ID: 35478898
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
 
LVL 31

Expert Comment

by:hnasr
ID: 35479110
Vheck with this:
table:a
datefield: dd

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

Author Closing Comment

by:lapucca
ID: 35481541
Thanks everyone.
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Suggested Courses

840 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question