T-SQL Querying (2015)

---

Identifying weekdays

Suppose that in your application you need to identify the weekday number of an input date. You need to be aware of some complexities involved in such a calculation, and this section provides the details.

To calculate the weekday number of a given date, you can use the DATEPART function with the weekday part (or dw for short), as in DATEPART(weekday, SYSDATETIME()) for today’s weekday number. You need to be aware that this calculation is language dependent. In the previous section, I explained that your effective language determines a related session option called DATEFORMAT, which in turn determines how date literals are interpreted. In a similar way, your language determines a session option called DATEFIRST, which in turn determines what’s considered the first day of the week (1 means Monday, 2 means Tuesday, ..., 7 means Sunday). You can query the effective setting using the @@DATEFIRST function and overwrite it using the SET DATEFIRST command. However, generally changing such language settings is not recommended because there could be calculations in the session that depend on the login’s language perspective. Plus, a cached query plan created with certain language settings cannot be reused by a session with different language settings.

Suppose today is a Thursday and you are computing today’s weekday number using the expression DATEPART(weekday, SYSDATETIME()). If you are connected with US English as your language, SQL Server sets your session’s DATEFIRST setting to 7 (indicating Sunday is the first day of the week); therefore, the expression returns the weekday number 5. If you are connected with British, SQL Server sets DATEFIRST to 1 (indicating Monday is the first day of the week); therefore, the expression returns the weekday number 4. If you explicitly set DATEFIRST to, say,2 (indicating Tuesday is the first day of the week), the expression returns 3. This is illustrated in Figure 7-1, with the arrow marking the DATEFIRST setting and x being the 1-based ordinal representing the result of the expression.

FIGURE 7-1 Language-dependent weekday.

You might run into cases where you need the weekday-number computation to assume your chosen first day of the week, ignoring the effective one. You do not want to overwrite any of the existing session’s language-related settings; rather, you want to somehow control this in the calculation itself. I can suggest a couple of methods to achieve this. I’ll refer to one method as the diff and modulo method and the other as the compensation method. For the sake of the example, suppose you want to consider Monday as the first day of the week in your calculation.

With the diff and modulo method you choose a date from the past that falls on the same weekday as the one you want to consider as the first day of the week. For this purpose, it’s convenient to use dates from the week of the base date (starting with January 1, 1900). That’s because the day parts and respective weekdays during this week are nicely aligned with the numbers and respective weekdays that DATEFIRST uses (1 for Monday, 2 for Tuesday, and so on). As an aside, curiously, for January 1, 1 was also a Monday. So, to consider Monday as the first day of the week, use the date January 1, 1900. Compute the difference in terms of days between that date and the target date (call this difference diff). The computation diff % 7 (% is modulo in T-SQL) will give you 0 for a target date whose weekday is a Monday (because diff is a multiplication of 7 in such a case), 1 for a Tuesday, and so on. Because you want to get 1 for the first day of the week, 2 for the second, and so on, simply add 1 to the computation. Here’s the complete expression in T-SQL giving you the weekday number of today’s date when considering Monday as the first day of the week:

SELECT DATEDIFF(day, '19000101', SYSDATETIME()) % 7 + 1;

To consider Sunday as the first day of the week, use the value ‘19000107’ as the starting point. If there’s some reason for you to consider Tuesday as the first day of the week, use the value ‘19000102’ as the starting point, and so on.

Another method to compute the weekday number with control over when the week starts is what I refer to as the compensation method. You use the DATEPART function with the weekday part, but instead of applying the function to the original input date, you apply it to an adjusted input date to compensate for the effect of changes in the DATEFIRST setting. Adjusting the input date by moving it @@DATEFIRST days forward neutralizes the effect of direct or indirect changes to the DATEFIRST setting.

To understand how this works, first make sure you have some coffee beside you, and then consider the following.

Let a be the original input date, and let b be the effective DATEFIRST setting (also the output of @@DATEFIRST). Let x be the original computation DATEPART(weekday, a), which is language dependent. To get a language-neutral computation, use DATEPART(weekday, DATEADD(day, b, a)). If b changes by a certain delta (such as a different language resulting in a different DATEFIRST setting), both the starting point for the calculation and the adjusted date change by the same delta, meaning that the calculation’s result remains the same. In other words, adding b (@@DATEFIRST) days to the date you’re checking compensates for any direct or indirect changes to DATEFIRST.

Now you know that regardless of the login’s language and the effective DATEFIRST setting, the computation DATEPART(weekday, DATEADD(day, @@DATEFIRST, @input)) will always return the same output for the same input. By default, the calculation behaves as if Sunday is the first day of the week. This means it will always return 1 for a Sunday, 2 for a Monday, and so on. Try it. If you want to consider a different weekday as the first day of the week, you need to further adjust the input by subtracting a constant number of days (call it c). Luckily, c and the weekday it represents are aligned with the numbers and respective weekdays DATEFIRST uses (1 for Monday, 2 for Tuesday, and so on). So, if you want to consider Monday as the first day of the week, use the expression DATEPART(weekday, DATEADD(day, @@DATEFIRST – 1, @input)). You can test the calculation with today’s date as input by using the following code:

SELECT DATEPART(weekday, DATEADD(day, @@DATEFIRST - 1, SYSDATETIME()));

The compensation method is illustrated in Figure 7-2, using today’s date as input, assuming today is a Thursday. Remember a is the input date, b is @@DATEFIRST, c is the constant you need to subtract (1 for Monday), and x is the result of the original computationDATEPART(weekday, a).

FIGURE 7-2 Language-neutral weekday.

The illustration to the left represents an environment with DATEFIRST set to 1 (where normally Monday is the first day of the week), and the illustration to the right represents an environment with DATEFIRST set to 2 (Tuesday is the first day of the week). Observe how in both cases the result is 4 assuming Monday is the first day of the week for the calculation.

Handling date-only or time-only data with DATETIME and SMALLDATETIME

As mentioned earlier, the more veteran types DATETIME and SMALLDATETIME are still quite widely used, especially the former, for historic and other reasons. The question is, how do you handle date-only and time-only data with these types, considering that they contain both elements? The recommended practice is that when you need to store the date only, you store the date with midnight as the time; and when you need to store time only, you store the time with the base date (January 1, 1900) as the date.

If you’re wondering why you specifically use midnight and the base date and not other choices, there’s a reason for this. When SQL Server converts a character string that contains the date only to a date and time type, it assumes midnight as the time. As an example, say you have a query with the filter WHERE dt = ‘20150212’, and the dt column is of a DATETIME type. SQL Server will implicitly convert the literal character string to DATETIME, assuming midnight as the time. If you stored all values in the dt column with midnight as the time, this filter will correctly return the rows where the date is February 12, 2015.

As an example for time-only data, say you have a query with the filter WHERE tm = ‘12:00:00.000’ and the tm column is of a DATETIME type. SQL Server will implicitly convert the literal character string to DATETIME, assuming the base date as the date. If you stored all values in thetm column with the base date as the date, this filter will correctly return the rows where the time is midnight.

Based on the recommended practice, you need to be able to take an input date and time value, like the SYSDATETIME function, and convert it to DATETIME or SMALLDATETIME, setting the time to midnight to capture date-only data or setting the date to the base date to capture time-only data. To capture date-only data, simply convert the input to DATE and then to DATETIME (or SMALLDATETIME), like so:

SELECT CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME);

There’s a slightly more complex way to set the time part of the input to midnight, but it’s worthwhile to know it because the concept can be used in other calculations, as I will demonstrate in the next section. The method involves using a starting point that is a date from the past with midnight as the time. I like to use January 1, 1900 at midnight for this purpose. You compute the difference in terms of days between that starting point and the input value (call that difference diff). Then you add diff days to the same starting point you used to compute the difference, and you get the target date at midnight as the result. Here’s the complete calculation applied to SYSDATETIME as the input:

SELECT DATEADD(day, DATEDIFF(day, '19000101', SYSDATETIME()), '19000101');

To capture time-only data, convert the input to TIME and then to DATETIME (or SMALLDATETIME), like so:

SELECT CAST(CAST(SYSDATETIME() AS TIME) AS DATETIME);

First, last, previous, and next date calculations

This section covers calculations such as finding the date of the first or last day in a period, the date of the last or next occurrence of a certain weekday, and so on. All calculations that I’ll cover are with respect to some given date and time value. I’ll use the SYSDATETIME function as the given value, but you can change SYSDATETIME to any date and time value.

First or last day of a period

This section covers calculations of the first and last date in a period, such as the month or year, with respect to some given reference date and time value.

Earlier I provided the following expression to set the time part of a given date and time value to midnight:

SELECT DATEADD(day, DATEDIFF(day, '19000101', SYSDATETIME()), '19000101');

You can use similar logic to calculate the date of the first day of the month. You need to make sure you use an anchor date that is a first day of a month and use the month part instead of the day part, like so:

SELECT DATEADD(month, DATEDIFF(month, '19000101', SYSDATETIME()), '19000101');

This expression calculates the difference in terms of whole months between some first day of a month and the reference date. Call that difference diff. The expression then adds diff months to the anchor date, producing the date of the first day of the month corresponding to the given reference date.

An alternative method to compute the first day of the month is to use the DATEFROMPARTS function, like so:

SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), MONTH(SYSDATETIME()), 1);

To return the date of the last day of the month, you can use the previous calculation I showed for beginning of month, but with an anchor date that is an end of a month, like so:

SELECT DATEADD(month, DATEDIFF(month, '18991231', SYSDATETIME()), '18991231');

Note that it’s important to use an anchor date that is a 31st of some month, such as December, so that if the target month has 31 days the calculation works correctly.

Specifically with the end of month calculation, you don’t need to work that hard because T-SQL supports the EOMONTH function described earlier:

SELECT EOMONTH(SYSDATETIME());

To calculate the date of the first day of the year, use an anchor that is a first day of some year, and specify the year part, like so:

SELECT DATEADD(year, DATEDIFF(year, '19000101', SYSDATETIME()), '19000101');

Or you could use the DATEFROMPARTS function, like so:

SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), 1, 1);

To calculate the date of the last day of the year, use an anchor date that is the last day of some year:

SELECT DATEADD(year, DATEDIFF(year, '18991231', SYSDATETIME()), '18991231');

Or, again, you could use the DATEFROMPARTS function, like so:

SELECT DATEFROMPARTS(YEAR(SYSDATETIME()), 12, 31);

Previous or next weekday

This section covers calculations that return a next or previous weekday with respect to a given date and time value. I use the word respective to describe this sort of calculation.

Suppose you need to calculate the latest Monday before or on a given reference date and time. The calculation needs to be inclusive of the reference date. That is, if the reference date is a Monday, return the reference date; otherwise, return the latest Monday before the reference date. You can use the following expression to achieve this:

SELECT DATEADD(
day,
DATEDIFF(
day,
'19000101', -- Base Monday date
SYSDATETIME()) /7*7,
'19000101'); -- Base Monday date

The expression calculates the difference in terms of days between some anchor date that is a Monday and the reference date. Call that difference diff.

As I mentioned earlier, it’s convenient to use dates in the range January 1, 1900, and January 7, 1900, as anchor dates because they represent the weekdays Monday through Sunday, respectively. The day parts of the suggested anchor dates (1 through 7) are aligned with the integers used in SQL Server to represent the first day of the week; therefore, it’s easy to remember which day of the week each date in the range represents.

The expression then rounds the value down to the nearest multiple of 7 by dividing diff by 7 using integer division, and then multiplying it by 7. Call the result floor_diff. Note that the calculation of floor_diff will work correctly only when the result of DATEDIFF is nonnegative. So make sure you use an anchor date that is earlier than the reference date. The expression then adds floor_diff days to the anchor date, producing the latest occurrence of a Monday, inclusive. Remember that by inclusive I mean that if the reference date is a Monday, the calculation is supposed to return the reference date.

Here’s the expression formatted in one line of code:

SELECT DATEADD(day, DATEDIFF(day, '19000101', SYSDATETIME()) /7*7, '19000101');

Similarly, to return the date of the last Tuesday, use an anchor date that is a Tuesday:

SELECT DATEADD(day, DATEDIFF(day, '19000102', SYSDATETIME()) /7*7, '19000102');

And to return the date of the last Sunday, use an anchor date that is a Sunday:

SELECT DATEADD(day, DATEDIFF(day, '19000107', SYSDATETIME()) /7*7, '19000107');

To make the calculation exclusive of the reference date—meaning that you’re after the last occurrence of a weekday before the reference date (as opposed to on or before)—simply subtract a day from the reference date. For example, the following expression returns the date of the last occurrence of a Monday before the reference date:

SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(day, -1, SYSDATETIME())) /7*7,
'19000101');

To return the next occurrence of a weekday in an inclusive manner (on or after the reference date), subtract a day from the reference date and add 7 days to floor_diff. For example, the following expression returns the next occurrence of a Monday on or after the reference date:

SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(day, -1, SYSDATETIME())) /7*7 + 7,
'19000101');

Like before, replace the anchor date if you need to handle a different weekday—for example, Tuesday:

SELECT DATEADD(day, DATEDIFF(day, '19000102', DATEADD(day, -1, SYSDATETIME())) /7*7 + 7,
'19000102');

Or Sunday:

SELECT DATEADD(day, DATEDIFF(day, '19000107', DATEADD(day, -1, SYSDATETIME())) /7*7 + 7,
'19000107');

To make the calculation exclusive, meaning the next occurrence of a weekday after the reference date (as opposed to on or after), simply skip the step of subtracting a day from the anchor date. For example, the following expression returns the next occurrence of a Monday after the reference date:

SELECT DATEADD(day, DATEDIFF(day, '19000101', SYSDATETIME()) /7*7 + 7, '19000101');

This calculation is for the next occurrence of a Tuesday, exclusive:

SELECT DATEADD(day, DATEDIFF(day, '19000102', SYSDATETIME()) /7*7 + 7, '19000102');

This calculation is for the next occurrence of a Sunday, exclusive:

SELECT DATEADD(day, DATEDIFF(day, '19000107', SYSDATETIME()) /7*7 + 7, '19000107');

First or last weekday

In this section, I’ll describe calculations that return the first and last occurrences of a certain weekday in a period such as a month or year. To calculate the first occurrence of a certain weekday in a month, you need to combine two types of calculations I described earlier. One is the calculation of the first day of the month:

SELECT DATEADD(month, DATEDIFF(month, '19000101', SYSDATETIME()), '19000101');

The other is the calculation of the next occurrence of a weekday, inclusive—Monday, in this example:

SELECT DATEADD(day, DATEDIFF(day, '19000101', DATEADD(day, -1, SYSDATETIME())) /7*7 + 7,
'19000101');

The trick is to simply use the first day of the month calculation as the reference date within the next-weekday-occurrence calculation. For example, the following expression returns the first occurrence of a Monday in the reference month:

SELECT DATEADD(day, DATEDIFF(day, '19000101',
-- first day of month
DATEADD(month, DATEDIFF(month, '19000101', SYSDATETIME()), '19000101')
-1) /7*7 + 7, '19000101');

To handle a different weekday, replace the anchor date in the part of the expression that calculates the next occurrence of a weekday—not in the part that calculates the first month day. The following expression returns the date of the first occurrence of a Tuesday in the reference month:

SELECT DATEADD(day, DATEDIFF(day, '19000102',
-- first day of month
DATEADD(month, DATEDIFF(month, '19000101', SYSDATETIME()), '19000101')
-1) /7*7 + 7, '19000102');

To calculate the date of the last occurrence of a weekday in the reference month, you need to combine two calculations as well. One is the calculation of the last day of the reference month:

SELECT DATEADD(month, DATEDIFF(month, '18991231', SYSDATETIME()), '18991231');

The other is the calculation of the previous occurrence of a weekday, inclusive—Monday, in this example:

SELECT DATEADD(day, DATEDIFF(day, '19000101', SYSDATETIME()) /7*7, '19000101');

Simply use the last day of the month calculation as the reference date in the last-weekday calculation. For example, the following expression returns the last occurrence of a Monday in the reference month:

SELECT DATEADD(day, DATEDIFF(day, '19000101',
-- last day of month
DATEADD(month, DATEDIFF(month, '18991231', SYSDATETIME()), '18991231')
) /7*7, '19000101');

To address a different weekday, substitute the anchor date in the last weekday calculation with the applicable one. For example, the following expression returns the last occurrence of a Tuesday in the reference month:

SELECT DATEADD(day, DATEDIFF(day, '19000102',
-- last day of month
DATEADD(month, DATEDIFF(month, '18991231', SYSDATETIME()), '18991231')
) /7*7, '19000102');

In a manner similar to calculating the first and last occurrences of a weekday in the reference month, you can calculate the first and last occurrence of a weekday in the reference year. Simply substitute the first-month-day or last-month-day calculation with the first-year-day or last-year-day calculation. Following are a few examples.

The first occurrence of a Monday in the reference year:

SELECT DATEADD(day, DATEDIFF(day, '19000101',
-- first day of year
DATEADD(year, DATEDIFF(year, '19000101', SYSDATETIME()), '19000101')
-1) /7*7 + 7, '19000101');

The first occurrence of a Tuesday in the reference year:

SELECT DATEADD(day, DATEDIFF(day, '19000102',
-- first day of year
DATEADD(year, DATEDIFF(year, '19000101', SYSDATETIME()), '19000101')
-1) /7*7 + 7, '19000102');

The last occurrence of a Monday in the reference year:

SELECT DATEADD(day, DATEDIFF(day, '19000101',
-- last day of year
DATEADD(year, DATEDIFF(year, '18991231', SYSDATETIME()), '18991231')
) /7*7, '19000101');

The last occurrence of a Tuesday in the reference year:

SELECT DATEADD(day, DATEDIFF(day, '19000102',
-- last day of year
DATEADD(year, DATEDIFF(year, '18991231', SYSDATETIME()), '18991231')
) /7*7, '19000102');

Search argument

One of the most fundamental concepts in query tuning is that of a search argument (SARG). It’s not really unique to working with date and time data, but it is very common with such data. Suppose that you have a query with a filter in the form WHERE <column> <operator> <value> and a supporting index on the filtered column. As long as you don’t apply manipulation to the filtered column, SQL Server can rely on the index order—for example, to consider performing a seek followed by a range scan in the index. If the index is not a covering one, of course there would be the question of whether the selectivity of the filter is high enough to justify using the index, but the point is that the potential is there. If you do apply manipulation to the column, besides some exceptional cases, this might result in SQL Server not relying on the index order and using less optimal access methods like full scans.

As mentioned, the issue of SARGablility is not really unique to date and time data but is quite common in that scenario, because you often filter data based on date and time. A classic example is filtering range-of-date and time values like a whole day, week, month, quarter, year, and so on. To allow optimal index usage, you should get into the habit of expressing the range filter without manipulating the filtered column. As an example, the following two queries are logically equivalent—both filtering the entire order year 2014:

USE PerformanceV3;

SELECT orderid, orderdate, filler
FROM dbo.Orders
WHERE YEAR(orderdate) = 2014;

SELECT orderid, orderdate, filler
FROM dbo.Orders
WHERE orderdate >= '20140101'
AND orderdate < '20150101';

There’s a clustered index defined on the orderdate column, so clearly the optimal plan here is to perform a seek followed by a range scan of the qualifying rows in the index. Figure 7-3 shows the actual plans you do get for these queries.

FIGURE 7-3 Non-SARG versus SARG.

The predicate in the first query is non SARGable. The optimizer doesn’t try to be smart and understand the meaning of what you’re doing; rather, it just resorts to a full scan. With the second query, the predicate is SARGable, allowing an efficient seek and partial scan in the index.

As mentioned, there are exceptions where SQL Server will consider a predicate that applies manipulation to the filtered column as SARGable. An example for such an exception is when the predicate converts the column to the DATE type, as in the following query:

SELECT orderid, orderdate, filler
FROM dbo.Orders
WHERE CAST(orderdate AS DATE) = '20140212';

Microsoft added logic to the optimizer to consider such a predicate as SARGable, and hence it chooses here to perform a seek and a range scan in the index. The problem is, this example is the exception and not the norm.

If you’re an experienced database practitioner, it’s likely that other people mimic your coding practices without always fully understanding them. For this reason, as a general rule, you should prefer the form without manipulation whenever possible because that’s the one that is more likely to be SARGable. So instead of using the predicate form in the preceding query, it’s recommended to use the following form:

SELECT orderid, orderdate, filler
FROM dbo.Orders
WHERE orderdate >= '20140212'
AND orderdate < '20140213';

Rounding issues

When you convert a character-string value to a date and time type, or convert one from a higher level of precision to a lower one, you need to be aware of how SQL Server handles such conversions. Some find SQL Server’s treatment surprising.

One of the common pitfalls occurs when converting a character-string value to the DATETIME type. To demonstrate this, I’ll use the following sample data:

USE TSQLV3;
IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders;
GO

SELECT * INTO Sales.MyOrders FROM Sales.Orders;
ALTER TABLE Sales.MyOrders ALTER COLUMN orderdate DATETIME NOT NULL;
CREATE CLUSTERED INDEX idx_cl_od ON Sales.MyOrders(orderdate);

Suppose you need to filter the orders where the order date falls within a certain date and time range. Imagine that the time part in the stored values wasn’t necessarily midnight and you want to filter a range like a whole day, month, year, and so on. For example, filter only the orders placed on January 1, 2015. A common yet incorrect way for people to express the filter is using the BETWEEN predicate, like so:

SELECT orderid, orderdate, custid, empid
FROM Sales.MyOrders
WHERE orderdate BETWEEN '20150101' AND '20150101 23:59:59.999';

When using this form, people think that 999 is the last expressible millisecond part of the second. However, recall that the precision of the DATETIME type is 3 1/3 milliseconds, rounded to the nearest tick. So the only valid values for the last digit of the milliseconds part are 0, 3, and 7. Any other value gets rounded to the closest supported one. This means that the value specified as the end delimiter in the preceding query is rounded up to midnight in the next day. This causes the query to return the following output:

orderid orderdate custid empid
----------- ----------------------- ----------- -----------
10808 2015-01-01 00:00:00.000 55 2
10809 2015-01-01 00:00:00.000 88 7
10810 2015-01-01 00:00:00.000 42 2
10811 2015-01-02 00:00:00.000 47 8
10812 2015-01-02 00:00:00.000 66 5

Observe that even though you were looking for only orders placed on January 1, 2015, you’re also getting the ones from the 2nd. For this reason, the recommended practice is to express your range as greater than or equal to midnight of the first date in the range and less than midnight of the date immediately following the last date in the range. In our example, you should change your filter like so:

SELECT orderid, orderdate, custid, empid
FROM Sales.MyOrders
WHERE orderdate >= '20150101'
AND orderdate < '20150102';

And this time you get the correct result:

orderid orderdate custid empid
----------- ---------- ----------- -----------
10808 2015-01-01 55 2
10809 2015-01-01 88 7
10810 2015-01-01 42 2

If you follow this practice, your code will work correctly with all date and time types, whether the values include a relevant time portion or not. It’s always good to get used to forms that work correctly in all cases.

Back to our example with the DATETIME column, to return orders placed today manipulate the result of the SYSDATETIME function to compute today at midnight and tomorrow at midnight as the delimiters of the closed-open interval, like so:

SELECT orderid, orderdate, custid, empid
FROM Sales.MyOrders
WHERE orderdate >= CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME)
AND orderdate < DATEADD(day, 1, CAST(CAST(SYSDATETIME() AS DATE) AS DATETIME));

When you’re done, run the following code for cleanup:

IF OBJECT_ID(N'Sales.MyOrders', N'U') IS NOT NULL DROP TABLE Sales.MyOrders;

You might not be aware that SQL Server applies similar rounding logic also when converting from a higher precision date and time value to a lower precision one. For example, when you convert the result of the SYSDATETIME function (returns a DATETIME2 value) to SMALLDATETIME (minute precision), SQL Server doesn’t floor the value to the bottom of the minute; rather, it rounds the value to the closest minute. This means the value is rounded to the beginning of the minute before the half-minute point and to the next minute on or beyond that point. If you want to apply flooring instead of rounding logic, subtract 30 seconds before converting the value. The following code returns the result of the SYSDATETIME function, demonstrating both the rounding and flooring of the value:

SELECT
SYSDATETIME() AS currentdatetime,
CAST(SYSDATETIME() AS SMALLDATETIME) AS roundedtominute,
CAST(DATEADD(ss, -30, SYSDATETIME()) AS SMALLDATETIME) AS flooredtominute;

Suppose you ran this code when SYSDATETIME returns ‘2015-02-12 13:47:53.7996475’. You will get the following output:

currentdatetime roundedtominute flooredtominute
--------------------------- ----------------------- -----------------------
2015-02-12 13:47:53.7996475 2015-02-12 13:48:00 2015-02-12 13:47:00

The same rounding logic applies with any conversion from a higher precision date and time value to a lower one. For example, convert the result of SYSDATETIME to DATETIME(0), and you get rounding to the closest second. If you want to floor the value to the beginning of the second, subtract 500 milliseconds before converting. Here’s the code demonstrating both rounding and flooring with a granularity of a second:

SELECT
SYSDATETIME() AS currentdatetime,
CAST(SYSDATETIME() AS DATETIME2(0)) AS roundedtosecond,
CAST(DATEADD(ms, -500, SYSDATETIME()) AS DATETIME2(0)) AS flooredtosecond;

Again, assume you ran this code when SYSDATETIME returns ‘2015-02-12 13:47:53.7996475’. You will get the following output:

currentdatetime roundedtosecond flooredtosecond
--------------------------- --------------------------- ---------------------------
2015-02-12 13:47:53.7996475 2015-02-12 13:47:54 2015-02-12 13:47:53

Querying date and time data

This section covers the handling of querying tasks involving date and time data. It starts by providing a method to group data by week, and then provides solutions to various querying tasks involving date and time intervals.

Grouping by the week

Suppose you need to query the Sales.OrderValues view, group the rows by the order week (based on the order date), and return for each group the count of orders and total order values. As it turns out, the task is not as trivial as it might have seemed at the beginning. The main challenge is to compute a common week identifier for all dates that are associated with the same week. If you’re thinking of the DATEPART function with the week part (or wk or ww for short), this part gives you the week number in the year. If a week starts in one year and ends in another, the first few days will give you a different week number than the last few days. You need a different solution.

The solution I like to use is to compute for each date the respective start-of-week date, and then use that as my week identifier. The first step in the calculation is to compute the weekday number of the input date (call it wd). This is done using the DATEPART function with the weekday part: DATEPART(weekday, orderdate). Remember that this calculation is language dependent; it reflects the effective DATEFIRST setting based on the language of the login running the code. If you want the login’s perspective to determine when the week starts, you have your final wdvalue and therefore you’re done with the first step. If you need the calculation itself to control where the week starts, you have two different methods to achieve this as I explained earlier—the diff and modulo method, and the compensation method. For example, using the compensation method and considering Monday as the first day of the week, use the expression DATEPART(weekday, DATEADD(day, @@DATEFIRST –1, orderdate)).

Now that you have wd, the second step is to compute the distance of the input date from the respective start-of-week date (call it dist). Fortunately, that’s a simple calculation: a date with wd = N is at a distance of N – 1 days from the respective start-of-week date. For example, a date withwd = 3 is 2 days away from the respective start-of-week date. So the expression to compute dist is wd – 1.

The third and last step is to compute the respective start-of-week date (call it startofweek). That’s done by subtracting dist days from orderdate using the following expression: DATEADD(day, –dist, orderdate). You now have your week identifier and can use it as the only element in the grouping set of your grouped query.

Here’s the complete solution code:

USE TSQLV3;

SELECT
startofweek,
DATEADD(day, 6, startofweek) AS endofweek,
SUM(val) AS totalval,
COUNT(*) AS numorders
FROM Sales.OrderValues
CROSS APPLY ( VALUES( DATEPART(weekday, DATEADD(day, @@DATEFIRST -1, orderdate)) ) ) AS A1(wd)
CROSS APPLY ( VALUES( wd - 1 ) ) AS A2(dist)
CROSS APPLY ( VALUES( DATEADD(day, -dist, orderdate) ) ) AS A3(startofweek)
GROUP BY startofweek;

Observe that to also return the end-of-week date (call it endofweek), you simply add six days to startofweek. This query generates the following output:

startofweek endofweek totalval numorders
----------- ---------- --------- -----------
2013-07-01 2013-07-07 2303.40 2
2013-07-08 2013-07-14 10296.48 6
2013-07-15 2013-07-21 5306.03 6
2013-07-22 2013-07-28 4675.99 5
2013-07-29 2013-08-04 8160.00 6
...
2015-04-06 2015-04-12 21074.05 17
2015-04-13 2015-04-19 52976.83 17
2015-04-20 2015-04-26 15460.63 16
2015-04-27 2015-05-03 21720.42 17
2015-05-04 2015-05-10 12885.07 11

Intervals

An interval in mathematics is the set of values between some lower and upper values. A date and time interval is, therefore, the set of date and time values between some lower and upper values, with the granularity being based on the data type’s precision. The need to store and manipulate date and time intervals in databases is quite common. They represent things like sessions, chats, phone calls, validity periods, appointments, contracts, projects, patients’ visits to hospitals, and so on.

Common querying tasks related to intervals include identifying intervals that intersect with an input one, computing the maximum number of concurrent intervals, packing intervals, and others. Creating correct solutions for such tasks is not all that difficult. There are fairly simple classic solutions. But as it turns out, creating solutions that are both correct and perform well is far from being trivial. Achieving good performance is tricky mainly because of the fundamental optimization challenges I described in Chapter 2, “Query tuning,” concerning filters with multiple range predicates. Here you will see practical examples where this problem manifests itself. Fortunately, there are efficient solutions, although they are not as simple as the classic ones.

To demonstrate how to solve common tasks involving date and time intervals, I will use sample data representing mobile phone accounts and phone call sessions. Run the following code to create and populate the Accounts and Sessions tables in tempdb with a small set of sample data:

SET NOCOUNT ON;
USE tempdb;

IF OBJECT_ID('dbo.Sessions') IS NOT NULL DROP TABLE dbo.Sessions;
IF OBJECT_ID('dbo.Accounts') IS NOT NULL DROP TABLE dbo.Accounts;

CREATE TABLE dbo.Accounts
(
actid INT NOT NULL,
CONSTRAINT PK_Accounts PRIMARY KEY(actid)
);
GO

INSERT INTO dbo.Accounts(actid) VALUES(1), (2), (3);

CREATE TABLE dbo.Sessions
(
sessionid INT NOT NULL IDENTITY(1, 1),
actid INT NOT NULL,
starttime DATETIME2(0) NOT NULL,
endtime DATETIME2(0) NOT NULL,
CONSTRAINT PK_Sessions PRIMARY KEY(sessionid),
CONSTRAINT CHK_endtime_gteq_starttime
CHECK (endtime >= starttime)
);
GO

INSERT INTO dbo.Sessions(actid, starttime, endtime) VALUES
(1, '20151231 08:00:00', '20151231 08:30:00'),
(1, '20151231 08:30:00', '20151231 09:00:00'),
(1, '20151231 09:00:00', '20151231 09:30:00'),
(1, '20151231 10:00:00', '20151231 11:00:00'),
(1, '20151231 10:30:00', '20151231 12:00:00'),
(1, '20151231 11:30:00', '20151231 12:30:00'),
(2, '20151231 08:00:00', '20151231 10:30:00'),
(2, '20151231 08:30:00', '20151231 10:00:00'),
(2, '20151231 09:00:00', '20151231 09:30:00'),
(2, '20151231 11:00:00', '20151231 11:30:00'),
(2, '20151231 11:32:00', '20151231 12:00:00'),
(2, '20151231 12:04:00', '20151231 12:30:00'),
(3, '20151231 08:00:00', '20151231 09:00:00'),
(3, '20151231 08:00:00', '20151231 08:30:00'),
(3, '20151231 08:30:00', '20151231 09:00:00'),
(3, '20151231 09:30:00', '20151231 09:30:00');

A phone call session is an interval defined by the delimiters starttime and endtime with a granularity of a second using the type DATETIME2(0). I will leave the aspect of whether each of the delimiters is an open (exclusive) or closed (inclusive) one undefined so that you can define it on a case-by-case basis, depending on what real-life scenario you need the sample data to represent for you.

Use the small set of sample data created by the preceding code to verify the correctness of the solutions. For performance testing, you can use the following code, which creates a large set of sample data with 50 accounts and 10,000,000 intervals:

-- 10,000,000 intervals
DECLARE
@num_accounts AS INT = 50,
@sessions_per_account AS INT = 200000,
@start_period AS DATETIME2(3) = '20120101',
@end_period AS DATETIME2(3) = '20160101',
@max_duration_in_seconds AS INT = 3600; -- 1 hour

TRUNCATE TABLE dbo.Sessions;
TRUNCATE TABLE dbo.Accounts;

INSERT INTO dbo.Accounts(actid)
SELECT A.n AS actid
FROM TSQLV3.dbo.GetNums(1, @num_accounts) AS A;

WITH C AS
(
SELECT A.n AS actid,
DATEADD(second,
ABS(CHECKSUM(NEWID())) %
(DATEDIFF(s, @start_period, @end_period) - @max_duration_in_seconds),
@start_period) AS starttime
FROM TSQLV3.dbo.GetNums(1, @num_accounts) AS A
CROSS JOIN TSQLV3.dbo.GetNums(1, @sessions_per_account) AS I
)
INSERT INTO dbo.Sessions WITH (TABLOCK) (actid, starttime, endtime)
SELECT actid, starttime,
DATEADD(second,
ABS(CHECKSUM(NEWID())) % (@max_duration_in_seconds + 1),
starttime) AS endtime
FROM C;

Feel free to change the parameters for the sample data if you want to test the solutions with different data characteristics.

Intersection

An interval intersection test checks whether two intervals have any form of intersection. James F. Allen defines 13 relations between intervals, which are known as Allen’s interval algebra. (For details, see http://www.ics.uci.edu/~alspaugh/cls/shr/allen.html.) Out of the 13, 11 relations represent different forms of intersection. Those are all of Allen’s relations except before and after.

As an example, suppose you get as inputs an account id (@actid) and a closed interval [@s, @e] (with @s for start and @e for end), and your task is to return all account intervals from the Sessions table that intersect with the input one. The classic way in SQL to identify intersection is actually quite straightforward, using the predicates starttime <= @e AND endtime >= @s. These two range predicates are specified in addition to the equality predicate actid = @actid. The problem is that this classic method is often very inefficient in T-SQL. Recall the discussion about multiple range predicates in Chapter 2. Whether you create an index on the key list (actid, starttime, endtime) or (actid, endtime, starttime), only one range predicate can be used as a seek predicate (in addition to any equality predicates against leading keys). All rows that satisfy the seek predicates are scanned, and then the remaining range predicates are applied to the scanned rows as residual predicates.

You could be lucky if most queries target a limited period at the edge of the entire period covered by your data, constituting a small percentage of it. For example, usually for billing purposes you need to look for intervals that intersect only with a very recent period (last month/week). But then it’s very easy to fall into a trap from an indexing standpoint. The intuitive key order for most people to define in the index is (starttime, endtime); that’s following the equality column actid, of course. Remember that when you have multiple range predicates in your filter, you want the more selective one to appear first so that the range scan in the index leaf needs to scan a smaller range. Now think about it; which of the predicates is more selective when querying a recent period: starttime <= @e or endtime >= @s? Clearly, it’s the latter. But with an index on (starttime,endtime), your leading range key is the far less selective one, so the range scan will end up scanning most rows for the account in question.

To demonstrate this, first create the intuitive but less efficient index by running the following code:

CREATE UNIQUE INDEX idx_start_end ON dbo.Sessions(actid, starttime, endtime, sessionid);

Suppose you need to look for active sessions for account ID 1, during the hour between 11:00 AM and noon in the last day recorded (December 31, 2015). You issue the following query (RECOMPILE is used to apply parameter embedding):

DECLARE
@actid AS INT = 1,
@s AS DATETIME2(0) = '20151231 11:00:00',
@e AS DATETIME2(0) = '20151231 12:00:00';

SELECT sessionid, actid, starttime, endtime
FROM dbo.Sessions
WHERE actid = @actid
AND starttime <= @e
AND endtime >= @s
OPTION(RECOMPILE);

Here’s the output you get with the small set of sample data just to verify the validity of the code:

sessionid actid starttime endtime
---------- ------ -------------------- --------------------
4 1 2015-12-31 10:00:00 2015-12-31 11:00:00
5 1 2015-12-31 10:30:00 2015-12-31 12:00:00
6 1 2015-12-31 11:30:00 2015-12-31 12:30:00

Figure 7-4 illustrates how much data needs to be scanned in the leaf of the intuitive yet less efficient index (to the left) versus the less intuitive but more efficient one (to the right).

FIGURE 7-4 Indexing strategies for interval intersection queries.

The figure shows which range predicate is applied as a seek predicate for determining how many rows need to be scanned in the index leaf, and which predicate is applied as a residual predicate and evaluated against all remaining rows. As you can see, with an index on (starttime,endtime) most rows for account ID 1 need to be scanned, whereas with an index on (endtime, starttime) only a small percentage needs to be scanned.

Figure 7-5 shows the actual plan I got for the query against the large set of sample data.

FIGURE 7-5 Interval intersection with index idx_start_end.

Observe that the less selective predicate starttime <= ‘2015-12-31 12:00:00’ appears as a seek predicate and the more selective one endtime >= ‘2015-12-31 11:00:00’ appears as a residual predicate. I populated the table with 200,000 rows per account, and with about 300 rows fitting in a page, you get about 650 pages used per account. With the current index idx_start_end, most of those pages need to be scanned. When I ran this intersection query against the large set of sample data, it performed 648 logical reads.

The conclusion is that you’re better off creating the index with the more selective column endtime appearing before the less selective column starttime, like so:

CREATE UNIQUE INDEX idx_end_start ON dbo.Sessions(actid, endtime, starttime, sessionid);

Rerun the query:

DECLARE
@actid AS INT = 1,
@s AS DATETIME2(0) = '20151231 11:00:00',
@e AS DATETIME2(0) = '20151231 12:00:00';

SELECT sessionid, actid, starttime, endtime
FROM dbo.Sessions
WHERE actid = @actid
AND starttime <= @e
AND endtime >= @s
OPTION(RECOMPILE);

This time, you get the plan in Figure 7-6, showing the more selective column used as a seek predicate and the less selective one used as a residual predicate.

FIGURE 7-6 Interval intersection with index idx_end_start.

When I ran this query on my system, it performed only four logical reads!

When you’re done testing intersection queries, run the following code to drop the index idx_end_start (and keep the index idx_start_end for later examples):

DROP INDEX idx_end_start ON dbo.Sessions;

So, when you’re lucky enough to have most intersection queries applied to recent periods in your data, it’s easy to get good performance as long as you create the right index. However, if in your environment intersection queries can be applied to any period, you’re in trouble. Whether you create the index with starttime before endtime or the other way around, on average, every query will result in scanning half the rows for the account. The sample data I use has 200,000 rows per partition (account), so scanning half the rows is not necessarily a disaster, but what if you have millions per partition?

There’s a solution for intersection queries that works well in the more general cases. It is based on an ingenious model called the Relational Interval Tree (RI-tree) model. It was crafted by Hans-Peter Kriegel, Marco Pötke, and Thomas Seidl from the University of Munich, with further optimizations by Laurent Martin. The implementation of this model involves: (1) adding a column to the table representing what’s called the fork node in the model, (2) two B-tree-based indexes based on (forknode, starttime, key) and (forknode, endtime, key), and (3) new intersection queries that unify three disjoint sets of intersecting intervals, each using no more than one range predicate. This model and the improvements are downright beautiful and quite an impressive accomplishment. If you have the need to perform intersection queries in your application and you currently suffer from performance problems, it is certainly worthwhile getting familiar with it. You can find the details about the model, improvements, and implementation in SQL Server in the following resources:

“Interval Queries in SQL Server” by Itzik Ben-Gan (http://sqlmag.com/t-sql/sql-server-interval-queries)

“Managing Intervals Efficiently in Object-Relational Databases” by Hans-Peter Kriegel, Marco Pötke, and Thomas Seidl (http://www.dbs.ifi.lmu.de/Publikationen/Papers/VLDB2000.pdf)

“A Static Relational Interval Tree” by Laurent Martin (http://www.solidq.com/static-relational-interval-tree/)

“Advanced interval queries with the Static Relational Interval Tree” by Laurent Martin (http://www.solidq.com/advanced-interval-queries-static-relational-interval-tree/)

“Using the Static Relational Interval Tree with time intervals” by Laurent Martin (http://www.solidq.com/using-static-relational-interval-tree-time-intervals/)

Max concurrent intervals

Finding the maximum number of concurrent intervals is a classic task. With our Sessions table, you need to compute for each account the maximum number of sessions that were active concurrently. Such calculations are usually done for reasons like figuring out peak use for capacity planning, billing purposes, and so on.

As usual, you need to know whether the interval delimiters are closed (exclusive) or open (inclusive). You need to know this to tell which intervals are considered active during any given point in time ts. For example, for a closed, closed interval [starttime, endtime] the interval is active during ts if ts >= starttime AND ts <= endtime. For a closed, open interval [starttime, endtime] the interval is active during ts if ts >= starttime AND ts < endtime. For the sake of this example, I’ll assume the latter (closed, open intervals).

For the small set of sample data, your solution should generate the following output:

actid mx
----------- -----------
1 2
2 3
3 2

I’ll start with a traditional yet inefficient set-based solution that is based on a subquery, and then I’ll continue with much more efficient solutions that are based on window functions.

The traditional subquery-based solution will benefit from the index idx_start_end you created earlier. If your Sessions table currently doesn’t have this index, create it by running the following code:

CREATE UNIQUE INDEX idx_start_end ON dbo.Sessions(actid, starttime, endtime, sessionid);

The solution starts by identifying the points in time when the maximum number of intervals potentially falls. If you follow the arrow of time, the count of active intervals changes only when an interval starts or ends. In between events, the count remains the same. Furthermore, every time an interval starts, the count increases; whereas every time an interval ends, the count decreases. Therefore, the maximum count has to fall on one of the start events. So the solution starts by defining a CTE called P that returns start-event time stamps (call the column ts) when the maximum count potentially falls:

WITH P AS -- time points
(
SELECT actid, starttime AS ts FROM dbo.Sessions
)
SELECT actid, ts FROM P;

The second step is to define a CTE called C with a query against P. The query uses a subquery to count the number of active intervals at ts for the same account based on the predicate I provided earlier. Here’s the code implementing this step:

WITH P AS -- time points
(
SELECT actid, starttime AS ts FROM dbo.Sessions
),
C AS -- counts
(
SELECT actid, ts,
(SELECT COUNT(*)
FROM dbo.Sessions AS S
WHERE P.actid = S.actid
AND P.ts >= S.starttime
AND P.ts < S.endtime) AS cnt
FROM P
)
SELECT actid, ts, cnt FROM C;

This code generates the following output using the small set of sample data:

actid ts cnt
------ -------------------- ----
1 2015-12-31 08:00:00 1
1 2015-12-31 08:30:00 1
1 2015-12-31 09:00:00 1
1 2015-12-31 10:00:00 1
1 2015-12-31 10:30:00 2
1 2015-12-31 11:30:00 2
2 2015-12-31 09:00:00 3
2 2015-12-31 08:30:00 2
2 2015-12-31 08:00:00 1
2 2015-12-31 11:00:00 1
2 2015-12-31 11:32:00 1
2 2015-12-31 12:04:00 1
3 2015-12-31 08:00:00 2
3 2015-12-31 08:00:00 2
3 2015-12-31 08:30:00 2
3 2015-12-31 09:30:00 0