Dates & Times in SQL

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

Robin 14 Jul 2014