Finding a date range in SQL

    At work, we use log4net, and we have the appender (or logging output location) set up to be AdoNetAppender, so thus it logs to a SQL database. In the Log table, there is a column called [Date], and it has the sql type of datetime.


    Often, when querying the Log table, you only want to view the most recent dates. lets say within the last week. You could always ORDER BY [Date] DESC of course, but suppose we wanted more control than that, such as only last week's dates.

    The SQL keywords (and functions) that are relevant here are BETWEEN, GETDATE and DATEADD.

    Here is the SQL code:

 SELECT
     [ID],[Date],[Thread],[Level],[Logger],[Message],[Exception]
 FROM
     [DatabaseName].[dbo].[Log]
 WHERE
     [Date] BETWEEN
      DATEADD(dd, -7, GETDATE())
      AND
      DATEADD(dd,  1, GETDATE())
 ORDER BY

     [Date] DESC

    The BETWEEN keyword should be pretty self-explanatory, as should the GETDATE function. The secret sauce here lies within the DATEADD function.

    The SQL function DATEADD has this signature: DATEADD (datepart, number, date) 

    The DATEADD function adds a number to a component of DATETIME, in this case, days. This number can be negative to subtract time from a DATETIME, as is the case with our example. The datepart parameter is what determines what component of the DATETIME we are adding to. You can add as much as a year, or as little as a nanosecond (what, no picoseconds? *laugh*). Microsoft's Transact-SQL MSDN page for DATEADD supplies the following table for datepart:

DATEPART	ABBREVIATIONS
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

    In the example, I am subtracting 7 days from the current date. If you are making a stored procedure, this variable can be replaced with a parameter:

 CREATE PROCEDURE [dbo].[sp_GetLogEntriesRange]
     @RangeInDays int
 AS
 BEGIN
     DECLARE @DaysToAdd int
     SET @DaysToAdd = (0 - @RangeInDays)

     SELECT
      [ID],[Date],[Thread],[Level],[Logger],[Message],[Exception]
     FROM
      [DatabaseName].[dbo].[Log]
     WHERE
      [Date] BETWEEN
       DATEADD(dd, @DaysToAdd, GETDATE())
       AND
       DATEADD(dd,  1, GETDATE())
     ORDER BY
      [Date] DESC
 END

    Enjoy, I hope this helps!