2

I have a query that declares and sets a datetime variable at the very beginning:

declare @_NextAppt as datetime
set @_NextAppt = dateadd(dd,3,getdate())     

when I check it and execute a select @_NextAppt it returns three days from today which is what I want.

The problem occurs in my next select statement when I use

where table.apptdate = @_NextAppt 

The query returns no rows.

Here's the sample query:

select distinct PatID, StaffID  
from dbo.Appointments    
where PatID is not null and ApptDate = @_NextAppt

I can get everything to work if I replace getdate() with an exact date like '11/09/2016'. But that doesn't work long term for my report.

What am I missing? I'm relatively new to SQL and self taught so I'm sure there is some SQL quirk that I don't know about.

3

The problem is that GETDATE() returns a DATETIME. So the WHERE condition checks if any of the values in that column is equal to that exact date and time (+3 days).

You need to strip the time part, either by casting or by using a date variable:

declare @_NextAppt as datetime ;        -- use same type as the column

set @_NextAppt = dateadd(day, 3, cast(getdate() as date)) ;
      -- strips the time from getdate(),
      -- adds 3 days and
      -- does an implicit conversion to the variable's type (datetime)

We also don't know the type of the column. It would be good to use inclusive-exclusive conditions when comparing datetime values. The query below will work independent of whether your column is DATE or DATETIME or DATETIME2:

select distinct PatID, StaffID  
from dbo.Appointments    
where PatID is not null 
  and ApptDate >= @_NextAppt
  and ApptDate  < dateadd(day, 1, @_NextAppt) ;

If the column is DATE, you could of course use an equality condition:

---
where PatID is not null 
  and ApptDate = @_NextAppt ;

Necessary links to blog posts about good (and bad) practices with dates (note how I replaced the shorthand dd to the full day datepart when using the DATEADD() function above):

|improve this answer|||||
  • Thanks for the explanation. I appreciate it and will read the Bad Habits articles. – SueC Dec 20 '16 at 16:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.