What's wrong with the DateDiff here?

lapucca
lapucca used Ask the Experts™
on
I have a record that has active and CompletedImpForm both equal to true(or 1, they're bit) with
DateOfFirstVisit=2011-05-01 00:00:00.000

I try to run that just now and no record is selected.  5/15 - 5/1 , is 14 days then why didn't this record got selected?  How to fix this?  Thank you.
select StudyId,accountKey,emailAddress from dbo.Patients  
	where Datediff(d,getdate(), DateOfFirstVisit)%14 = 0
	and Active=1 and CompletedImpForm=1

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:
select StudyId,accountKey,emailAddress from dbo.Patients  
      where Datediff(dd, DateOfFirstVisit, getdate()) <= 0
      and Active=1 and CompletedImpForm=1

Commented:
The datediff between those two values is not 0.  That's why it is not coming back to you.  It is -14.
Ephraim WangoyaSoftware Engineer

Commented:
oops sorry

select StudyId,accountKey,emailAddress from dbo.Patients  
      where Datediff(dd, DateOfFirstVisit, getdate()) <= 14
      and Active=1 and CompletedImpForm=1
How to Generate Services Revenue the Easiest Way

This Tuesday! Learn key insights about modern cyber protection services & gain practical strategies to skyrocket business:

- What it takes to build a cloud service portfolio
- How to determine which services will help your unique business grow
- Various use-cases and examples

Author

Commented:
Thanks everyone.  I'm sorry that I didn't make it clear here with my question.  I want to select records with DateOfFirstVisit that is 2 weeks ago from today's date.  I only want to consider the "date" part of the data and NOT to consider "time" part.  Thank you
"Batchelor", Developer and EE Topic Advisor
Top Expert 2015
Commented:
One approach is to compare to the date-only part of getdate, which is a single-time convert only, and consider that comparing "2 weeks ago" to date part only means 13 days plus 1 second up to 14 days.

select StudyId,accountKey,emailAddress from dbo.Patients  
      where DateOfFirstVisit >= convert(datetime, convert(varchar(8), getdate(), 112))-14
           and DateOfFirstVisit <  convert(datetime, convert(varchar(8), getdate(), 112))-13
      and Active=1 and CompletedImpForm=1

Author

Commented:
Thanks Q.  What you said there makes sense.  However, I ran your query but still nothing is selected.  
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Certain there is data qualifying for the query? You could try by removing the "and ... -13" part, and check if there rows with "Active=1 and CompletedImpForm=1".

Author

Commented:
Hi Q,
I ran the query without the 2 condition as well before my last post to make sure there is no other issue

      select StudyId,accountKey,emailAddress from dbo.Patients  
      where DateOfFirstVisit >= convert(datetime, convert(varchar(8), getdate(), 112))-14
           and DateOfFirstVisit <  convert(datetime, convert(varchar(8), getdate(), 112))-13
Top Expert 2012

Commented:
WHERE DateOfFirstVisit BETWEEN DATEADD(day, -14, DATEDIFF(day, 0, GETDATE())) and DATEADD(day, -13, DATEDIFF(day, 0, GETDATE()))
Top Expert 2012

Commented:
Also, confirm that DateOfFirstVisit is in fact a datetime or smalldatetime column.

Author

Commented:
hi Ace,
The DateOfFirstVisit is a datetime column, I double checked it.
Your query returns a record with DateOfFirstVisit =2011-05-02 00:00:00.000
But that's not 2 weeks from today's date though.  Thank ;you.
Top Expert 2012
Commented:
You are right, I should not have committed the same mistake as previous solutions.
Try it this way:
WHERE DateOfFirstVisit > DATEADD(day, -15, DATEDIFF(day, 0, GETDATE()))
            DateOfFirstVisit <= DATEADD(day, -14, DATEDIFF(day, 0, GETDATE()))

Author

Commented:
Hi Ace,
I ran the new query but I got error : Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'DateOfFirstVisit'.

I cannot tell what's wrong here.  Thank you.

      select StudyId,accountKey,emailAddress from dbo.Patients  
            WHERE DateOfFirstVisit > DATEADD(day, -15, DATEDIFF(day, 0, GETDATE()))
            DateOfFirstVisit <= DATEADD(day, -14, DATEDIFF(day, 0, GETDATE()))
Alpesh PatelSolution Architect at TCS (SAFe®Agilist, TOGAF® Foundation, PSM I®, MCP, MCSD)
Commented:
select StudyId,accountKey,emailAddress from dbo.Patients  
        where Datediff(day,getdate(), DateOfFirstVisit)%14 = 0
        and Active=1 and CompletedImpForm=1
select StudyId,accountKey,emailAddress from dbo.Patients  
            WHERE DateOfFirstVisit > DATEADD(day, -15, DATEDIFF(day, 0, GETDATE())) AND
            DateOfFirstVisit <= DATEADD(day, -14, DATEDIFF(day, 0, GETDATE()))

Author

Commented:
Hi Patel,
That's kind of what I started with and I still get no record returned after running your query.  Thank you.

Author

Commented:
Thanks everyone.  I made a mistake by looking at another datetime colunns' data.  Once that's identified, all of you that got points your queries work including my original one. Thank you again.
Qlemo"Batchelor", Developer and EE Topic Advisor
Top Expert 2015

Commented:
Please be aware that "%14 = 0" will get all records with multiples of 14 days (14, 28, 42, ...), and is not correct. Wether you use -15, -14 or -14, -13 as limits is a matter of definition and taste ...
Top Expert 2012

Commented:
Not to mention the fact that the following will produce lousy performance:
where Datediff(day,getdate(), DateOfFirstVisit) any condition

Author

Commented:
Very good points.  Thank you Q and Ace.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial