Using GETDATE() in a variable returns 0 rows

Question

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.

Answer 1

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):

• Bad Habits to Kick : Using shorthand with date/time operations
• Bad habits to kick : mis-handling date / range queries
• What do BETWEEN and the devil have in common?

• SueC: Thanks for the explanation. I appreciate it and will read the Bad Habits articles.