Submitted by: Hannes du Preez(ojdupreez1978@gmail.com)

Thursday Apr 26th 2018 by Hannes du Preez

Language: SQL, Expertise: Beginner - See how to include all values in a date range by using minus 1 second trick.

Sometimes when doing a search for data between date ranges, some of the data is omitted because the search doesn't include the whole last day's values. Here is a small trick to always get all the data between a specified date range.

Declare @fromDate DATE
Declare @ToDate DATE
SELECT Date From Table
   WHERE.Date BETWEEN CAST(@fromDate AS DATE) and DATEADD(s,-1,DATEADD(d,1,@ToDate)) --To Date Minus 1 second to include the whole last day's values
Mobile Site | Full Site