Link to home
Start Free TrialLog in
Avatar of lapucca
lapucca

asked on

What's wrong with the DateDiff here?

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

Avatar of Ephraim Wangoya
Ephraim Wangoya
Flag of United States of America image

select StudyId,accountKey,emailAddress from dbo.Patients  
      where Datediff(dd, DateOfFirstVisit, getdate()) <= 0
      and Active=1 and CompletedImpForm=1
The datediff between those two values is not 0.  That's why it is not coming back to you.  It is -14.
oops sorry

select StudyId,accountKey,emailAddress from dbo.Patients  
      where Datediff(dd, DateOfFirstVisit, getdate()) <= 14
      and Active=1 and CompletedImpForm=1
Avatar of lapucca
lapucca

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca

ASKER

Thanks Q.  What you said there makes sense.  However, I ran your query but still nothing is selected.  
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".
Avatar of lapucca

ASKER

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
WHERE DateOfFirstVisit BETWEEN DATEADD(day, -14, DATEDIFF(day, 0, GETDATE())) and DATEADD(day, -13, DATEDIFF(day, 0, GETDATE()))
Also, confirm that DateOfFirstVisit is in fact a datetime or smalldatetime column.
Avatar of lapucca

ASKER

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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca

ASKER

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()))
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of lapucca

ASKER

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

ASKER

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.
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 ...
Not to mention the fact that the following will produce lousy performance:
where Datediff(day,getdate(), DateOfFirstVisit) any condition
Avatar of lapucca

ASKER

Very good points.  Thank you Q and Ace.