Wednesday, July 29, 2015

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!