My daily tasks often involve designing reports and providing business users with requested report data via ad-hoc queries. This means getting well acquainted with date handling in Microsoft SQL Server.
Below is a list of some common date handling tips for SQL Server queries. Many of these tips are explained in greater detail in The Ultimate Guide to the DateTime Data Types. That guide also explains the best ways to represent literal date values to avoid problems with regional settings and why to avoid using BETWEEN in date range queries.
Consider the case of a client application passing a date range (e.g. start & end dates) to a reporting stored procedure. If the procedure has parameters of type DATETIME or SMALLDATETIME occasionally bugs can occur where the application accidentally passes a time value in addition to causing subtle reporting errors that manifest differently depending on the time of day the procedure is run.
Where possible, a solution for this problem might be to restrict the data type of the parameters to the DATE type rather than DATETIME or SMALLDATETIME. What about when supporting legacy systems still on SQL Server 2000 where the DATE type is not available? Date truncation inside the procedure can be used to guard against errors in this scenario.
To truncate a datetime value to the nearest date:
Some applications may have a user defined function (UDF) that performs this task, for example:
My general rule when reporting over a single day or range of days is to use the DATE type if available. When not possible I prefer the DATEADD/DATEDIFF method of date truncation instead of UDFs or other methods of truncating a date such as using CAST and CONVERT with formatting code 112.
All methods are fine for truncating a single date. When used over large data sets however, calling functions on date values (both in built functions or UDFs) have the potential to cause performance issues by inhibiting sargability if used in a query WHERE clause for example.
Interval Truncation & Rounding
This truncation logic can also be applied to other date/time intervals, such as years/quarters/months/weeks/hours or blocks of N minutes. The following query demonstrates how to round up or down to some nearest desired time intervals:
Week Day Number
Determining the day of the week a particular date falls on is made trickier because the DATEPART(dw, …) function depends on the value of the DATEFIRST variable. The DATENAME function isn’t reliable either because it depends on the connection LANGUAGE setting.
Obtaining a consistent day number without altering LANGUAGE or DATEFIRST variables involves adding an offset (@@DATEFIRST) to the date before using the DATEPART function:
Month and Quarter IDs
Grouping data by month or quarter is made simpler when an OLAP date dimension table is available. There are a number of scripts available on the web to generate a table like this. In the past I’ve adapted a quite comprehensive and helpful example date population script found here.
Grouping data in a basic way by month or quarter in an ad-hoc query isn’t too difficult if a time dimension isn’t available though. A month or quarter integer identifier can be generated as an expression and perhaps used later in a table join.
A date range table can be useful when requirements dictate reporting at differing time granularity (perhaps specified by the user as a report parameter) or reporting on intervals that vary in length. Sometimes the different reporting periods may even overlap with each other.