It is very easy for people new to SQL to get caught out by dates that contain times, e.g. the getdate() function which returns the current date and time. For example a travel company might have a Booking table with a column BookingDate which just contains the date (not time) that a booking was made.
If you wanted to find out the number of bookings made yesterday you might reasonably expect to run the following SQL:
select count(BookingRef) from Booking where BookingDate = dateadd(day, -1, getdate())
However, this query will not return any results as the you will be looking for bookings made at the current time 'yesterday'. As the BookingDate field does not contain the time, a match will not be found.
So to get around it, you need to strip the time from the getdate() value. There are different ways to do this, but I have found that the following approach results in the best performance when working with large data sets:
select dateadd(dd, datediff(dd, 0, getdate()), 0)
When applied to the query above, you get:
select count(BookingRef) from Booking where BookingDate = dateadd(dd, datediff(dd, 0, getdate()), 0)
If, however, your BookingDate field also includes times, then you will need to search for bookings using a date range, as follows:
select count(BookingRef) from Booking where BookingDate < dateadd(dd, datediff(dd, 0, getdate()), 0) and BookingDate >= dateadd(dd, -1, dateadd(dd, datediff(dd, 0, getdate()), 0))