Expressing DATETIME literals for data entry in T-SQL is tricky business. When you need to express one, you use a character string that is implicitly convertible to DATETIME. If a string appears in a context where a DATETIME is expected–for example, as the target value of a DATETIME column in an INSERT or UPDATE statement–it will be implicitly converted to DATETIME. Also, when expressions contain operands with different datatypes, normally the highest in precedence determines the datatype of all operands. Datetime has a higher precedence than a character string. So, for example, if you compare two values, one of which is a DATETIME and the other is a character string, the character string gets implicitly converted to a DATETIME.

To add to the confusion, there are various conventions for expressing DATETIME values. The value ′02/12/06′ means different things to different people. When this string must be converted to a DATETIME, SQL Server will convert it based on the language settings of the session. The session’s language is determined by the login’s default language, but it can be overridden by using the SET LANGUAGE session option. You can also control how DATETIME literals comprising digits and separators are interpreted by using the SET DATEFORMAT option, specifying a combination of the characters d, m, and y. For example, mdy would mean month, day, year. By the way, SET LANGUAGE implicitly sets DATEFORMAT to match the language’s convention for date formatting.

So you have tools to control the way some DATETIME literals will be interpreted, but you realize that by issuing one of the aforementioned SET options, you’re changing the behavior of the whole session. What if other code that will end up running in your session is supposed to be dependent on the login’s default language? This consideration is especially important with international applications.

Whenever possible, I write code that is independent of any settings or switches in the system. There are two literal DATETIME formats in SQL Server that are independent of any settings. I particularly like the one of these formats that has no separators between the date portions: ′[yy]yymmdd[ hh:mi[:ss][.mmm]]′. Examples of DATETIME literals in this format are ′20060212′, ′060212′, and ′20060211 23:59:59.997′. The DATEFORMAT and LANGUAGE settings do not affect the interpretation of DATETIME strings in this format. If you would rather use separators between the date parts, you can use the other setting-independent format in SQL Server: ′yyyy-mm-ddThh:mi:ss[.mmm]′. An example of this format is ′2006-02-12T14:23:05′. The time portion cannot be omitted when this format is used.

Another technique you can use to specify DATETIME values is to explicitly convert the character string to a DATETIME using the T-SQL function CONVERT, specifying the option style parameter in the function call. For example, if you want to use the British/French style with two digits for the year, specify style 3: CONVERT(DATETIME, ′12/02/06′, 3). For the full list of supported styles, please refer to Books Online, under the subject CAST and CONVERT (Transact-SQL).

At some point, you may see a date or time literal such as {d ′2006-02-12′}. This is an ODBC format that can be used in some APIs. I wouldn’t recommend using such literals because even though they are independent of settings, they are API dependent.

Conversions between DATETIME values and strings are not always exact. For example, if ′20060923 03:23:47.001′ is converted to DATETIME, it will be rounded to the nearest three-hundredth of a second, which is a millisecond earlier than the string indicates. If the same string is converted to SMALLDATETIME, it will be rounded to the nearest minute, almost 13 seconds later than the string indicates. Datetime-to-string conversions are not always exact, either. When converted to a string format that includes milliseconds, DATETIME values will be rounded to the nearest millisecond. Otherwise, time fields not in the destination format will be truncated.

With regard to DATETIME, be aware that values that cannot be represented exactly are rounded to the nearest DATETIME value that can be represented. This behavior is inconsistent with conversions between some other datatypes (for example, from DECIMAL to INT) where the value is simply truncated. (For example, 10.99 DECIMAL is converted to 10 INT). The milliseconds part of a DATETIME datatype will match the pattern [0-9][0-9][037] when displayed to millisecond precision. If, for example, you specify 994 as the milliseconds part of a DATETIME value, the actual value stored will contain milliseconds, and it will appear to contain 993 milliseconds when viewed as a character string. The value 996 will be rounded to 997. The value 999 will be rounded to 000 in the next second. This can be especially tricky if you try to specify the last moment of a particular date. If you specify ′20060211 23:59:59.999′ SQL Server cannot exactly represent the value, and it is rounded to the nearest DATETIME value: ′20060212 00:00:00.000′. That’s why it’s not a good idea to use a filter such as the following when looking for rows where a DATETIME column (call it dt) falls on a particular date:

WHERE dt BETWEEN '20060211 00:00:00.000' AND '20060211 23:59:59.999'

Rows where dt is equal to ′20060212 00:00:00.000′ will also qualify. If you are certain dt will always be a DATETIME column, and never a SMALLDATETIME column, you could instead use 997 as the milliseconds portion in the upper bound. Better yet, use the following predicate, which works regardless of whether dt is DATETIME or SMALLDATETIME, and which does not depend on the particular precision of SQL Server’s DATETIME types:

WHERE dt >= '20060211 00:00:00.000' AND dt < '20060212 00:00:00.000'

Some programmers prefer to omit the time portion when using this form of predicate, in which case SQL Server will interpret the string as midnight on the specified date:

WHERE dt >= '20060211' AND dt < '20060212'

This predicate is a Search Argument (SARG), meaning that the optimizer can consider the potential of using an index seek operation. Although you can also write this predicate using a function to extract the date portion, the result is not a SARG, so for better performance, refrain from using a predicate such as the following:

WHERE CONVERT(VARCHAR(8), dt, 112) = '20060211'

The list of DATETIME functions supported by SQL Server is short, but the functions are handy. These functions perform correct DATETIME calculations and take into account leap years (to some extent) and other calendar details. Therefore, it’s advisable to use them whenever possible and avoid using your own string manipulations.

I’ll describe the list of functions briefly. I’ll make use of them in the "DATETIME-Related Querying Problems" section.

DATEADD allows you to add a specified number of some DATETIME unit to a given DATETIME value. For example, the expression DATEADD(month, 1, ′20060725 12:00:00.000′) adds one month to July 25, 2006, noon. Subtraction is achieved by adding a negative number of units.

Tip

To add or subtract a number of days, you can use the + and - operators with an integer as an alternative to using the DATEADD function. For example, DATEADD(day, 1, dt) is equivalent to dt + 1.

DATEDIFF calculates the difference in a specified date part between two DATETIME values. For example, the expression DATEDIFF(month, ′20060725′, ′20060825′) calculates the difference in months between July 25, 2006 and August 25, 2006.

Caution

The DATEDIFF function doesn’t take into consideration higher levels of granularity than the specified date part; rather, it takes into consideration only lower ones. For example, when you calculate this difference in years between two very close values: DATEDIFF(year, ′20061231 23:59:59.997′, ′20070101 00:00:00.000′), you get 1. DATEDIFF ignores those part of the DATETIME values that have a higher level of granularity than year (that is, it ignores the month, day, and so on).

Using DATEPART, you can extract a specified DATETIME part from a DATETIME value, and it gives an integer result. For example, the expression DATEPART(hour, ′20060118 14:39:05.370′) extracts the hour portion of the input DATETIME value. The YEAR, MONTH, and DAY functions are abbreviations of the DATEPART function that return the year, month, and day of the month, respectively. DATENAME is similar to DATEPART, but it returns the name of the date part (or the numeral if there is no name) as a character string. For example, the expression DATENAME(weekday, ′20060118 14:39:05.370′) returns the weekday name of the input DATETIME value.

Note that some of the results that you will get from expressions involving DATETIME functions are language-dependent. For example, the expression DATENAME(weekday, ′20060118 14:39:05.370′) will return ′wednesday′ if the session’s language is set to us_english, and ‘mercoledì’ if it is set to Italian.

GETDATE gives you the server’s local DATETIME value, and GETUTCDATE gives you the current Coordinated Universal Time (UTC), which is calculated from the server’s local time and time zone settings. Finally, CURRENT_TIMESTAMP is the ANSI form of GETDATE. The last three functions accept no arguments.

For more details about DATETIME functions, please consult Books Online.

SQL Server, at the time of this writing, has never supported separate DATE and TIME datatypes. Support for these types is probably one of the more necessary features missing from the product. Interestingly, early beta builds of SQL Server 2005 implemented DATE and TIME types, but as CLR user-defined types, not as native datatypes. These were eventually pulled out of the product because of compatibility issues with existing datatypes and code, and because they were non-standard in important ways. There wasn’t enough time left in the development schedule to implement those datatypes as native ones, or to make the required adjustments to the CLR UDTs, so unfortunately we don’t have separate DATE and TIME in SQL Server 2005. We might see them in the next version of SQL Server. Of course, if you like, you can create your own DATE and TIME as CLR-based UDTs.

For now, you need to make due with the whole DATETIME package, even when you need to represent only a date or only a time. When you need to specify only dates, you omit the time portion. When converted to DATETIME, such a value will still contain a time portion, but it will represent midnight. Similarly, when a character string with only a time portion is converted to DATETIME, SQL Server will set the date part to its base date of January 1, 1900.

If you care only about the date or only about the time, specify only the part you care about and allow SQL Server to assume the defaults I mentioned earlier. This will simplify manipulation of those values. For example, suppose that you want to create a DEFAULT constraint that stores the current date in a column named dt. If you simply use the GETDATE function as dt’s default, you will need to use range filters when looking for a particular date. Instead, extract only the date portion from GETDATE for the default definition. You will get that date at midnight. A recommended expression for dt’s default would be: DATEADD(d,DATEDIFF(d,0,GETDATE()),0) or CAST(CONVERT(CHAR(8),GETDATE(),112) AS DATETIME). Similar to the last expression, use styles 108 or 114 to extract only the time portion of GETDATE. The former’s highest granularity is seconds, whereas the latter’s is milliseconds.

The first DATETIME-related problem is to calculate the date of the nearest birthday based on a person’s birth date and today’s date. This problem demonstrates how to correctly handle leap years by using DATETIME-related functions. To be more specific, using the Employees table in the Northwind database, your query needs to return the date of each employee’s nearest birthday, as of today’s date, based on the stored BirthDate and GETDATE values. If this year’s birthday date has already passed, your query should return the birthday date for next year; otherwise, it should return this year’s date.

Note

DATEADD and other DATETIME functions only deal with leap years to the extent that they don’t ever give results like February 29, 2006. People still need to make definitions that take leap years into account. If the system’s default behavior does not meet your needs, you will need to make the appropriate adjustments to your expressions. The default behavior of DATEADD when adding a certain number of years to a date that falls on February 29, is to return a date with February 29 if the target year is a leap year, and February 28 if it isn’t. Most people born on February 29 in a leap year celebrate their birthday on March 1 in a non-leap year (also for many legal purposes), and that’s the approach that we will apply in our solution. For example, if today’s date is September 26, 2005, someone born on February 29, 1972 should get back from your query the nearest birthday date March 1, 2006. If today’s date is September 26, 2007, the query should return February 29, 2008.

Before you start working on a solution, run the following code, which adds two employees to the Employees table:

SET NOCOUNT ON;
USE Northwind;

INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
  VALUES('Leaping', 'George', '19720229'),
INSERT INTO dbo.Employees(LastName, FirstName, BirthDate)
  VALUES('Today', 'Mary', CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME));

George Leaping was born on February 29, 1972, and Mary Today was born today. Here’s the solution query:

WITH Args1 AS
(
  SELECT LastName, FirstName, BirthDate,
    DATEDIFF(year, BirthDate, GETDATE()) AS Diff,
    CAST(CONVERT(CHAR(8), GETDATE(), 112) AS DATETIME) AS Today
  FROM dbo.Employees
),
Args2 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    DATEADD(year, Diff, BirthDate) AS BDCur,
    DATEADD(year, Diff + 1, BirthDate) AS BDNxt
  FROM Args1
),
Args3 AS
(
  SELECT LastName, FirstName, BirthDate, Today,
    BDCur + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDCur) = 28
      THEN 1 ELSE 0 END AS BDCur,
    BDNxt + CASE WHEN DAY(BirthDate) = 29 AND DAY(BDNxt) = 28
      THEN 1 ELSE 0 END AS BDNxt
  FROM Args2
)
SELECT LastName, FirstName, BirthDate,
  CASE WHEN BDCur >= Today THEN BDCur ELSE BDNxt END AS BirthDay
FROM Args3;

The query defining the CTE Args1 calculates for each employee the difference in years between the birth date and today’s date (Diff), and it also calculates today’s date at midnight (Today). If you highlight and run only the query defining the CTE Args1, you get the output shown in Table 1-1, assuming today’s date is September 26, 2005.

To calculate the date of the nearest birthday for a given employee, you need to add Diff years to Birthdate. If the result is less than Today, you would need to add another year. The query defining the CTE Args2 adds to Args1 two attributes called BDCur and BDNxt which hold the birthday dates this year and next year, respectively. Note though, that if BirthDate falls on February 29, and the target date (BDCur or BDNxt) is not in a leap year, it will contain February 28 and not March 1. The query defining the CTE Args3 adjusts the dates in BDCur and BDNxt to March 1 if needed. The outer query returns BDCur as the nearest birthday if it is greater than or equal to today’s date, and BDNxt otherwise. The output of the solution query is shown in Table 1-2, again assuming today’s date is September 26, 2005.

You can see that George Leaping’s nearest birthday will occur next year, on March 1. Mary Today’s birthday is, not surprisingly, today.

To clean up, delete the two added employees by running the following code:

DELETE FROM dbo.Employees WHERE EmployeeID > 9;

Many temporal querying problems require you to identify overlapping periods. Here I’ll present a few such problems. In my examples, I’ll use the Sessions table, which you create and populate by running the code in Example 1-1.

USE tempdb;
GO
IF OBJECT_ID('dbo.Sessions') IS NOT NULL
  DROP TABLE dbo.Sessions;
GO

CREATE TABLE dbo.Sessions
(
  keycol    INT         NOT NULL IDENTITY PRIMARY KEY,
  app       VARCHAR(10) NOT NULL,
  usr       VARCHAR(10) NOT NULL,
  starttime DATETIME    NOT NULL,
  endtime   DATETIME    NOT NULL,
  CHECK(endtime > starttime)
);

INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user1', '20060212 08:30', '20060212 10:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user2', '20060212 08:30', '20060212 08:45'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user1', '20060212 09:00', '20060212 09:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user2', '20060212 09:15', '20060212 10:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user1', '20060212 09:15', '20060212 09:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user2', '20060212 10:30', '20060212 14:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user1', '20060212 10:45', '20060212 11:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app1', 'user2', '20060212 11:00', '20060212 12:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user1', '20060212 08:30', '20060212 08:45'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user2', '20060212 09:00', '20060212 09:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user1', '20060212 11:45', '20060212 12:00'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user2', '20060212 12:30', '20060212 14:00'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user1', '20060212 12:45', '20060212 13:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user2', '20060212 13:00', '20060212 14:00'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user1', '20060212 14:00', '20060212 16:30'),
INSERT INTO dbo.Sessions(app, usr, starttime, endtime)
  VALUES('app2', 'user2', '20060212 15:30', '20060212 17:00'),

CREATE UNIQUE INDEX idx_app_usr_s_e_key
  ON dbo.Sessions(app, usr, starttime, endtime, keycol);
CREATE INDEX idx_app_s_e ON dbo.Sessions(app, starttime, endtime);
GO

The Sessions table tracks sessions of users in applications for billing and other purposes. A session can represent an open connection to the Internet, for example, with some Internet provider that charges by connectivity time. Each row contains a key (keycol), application name (app), user name (usr), start time (starttime), and end time (endtime). Example 1-1 also creates indexes to speed the queries that I’ll present in my solutions. I’ll cover discuss three techniques that involve overlaps: identifying overlaps, grouping overlaps, and max overlaps.

S2.starttime BETWEEN S1.starttime AND S1.endtime
OR S1.starttime BETWEEN S2.starttime AND S2.endtime

SELECT S1.app, S1.usr,
  S1.keycol AS key1, S1.starttime AS start1, S1.endtime AS end1,
  S2.keycol AS key2, S2.starttime AS start2, S2.endtime AS end2
FROM dbo.Sessions AS S1
  JOIN dbo.Sessions AS S2
    ON S2.app = S1.app
    AND S2.usr = S1.usr
    AND (S2.starttime BETWEEN S1.starttime AND S1.endtime
         OR S1.starttime BETWEEN S2.starttime AND S2.endtime);

S2.endtime >= S1.starttime AND S2.starttime <= S1.endtime

SELECT S1.app, S1.usr,
  S1.keycol AS key1, S1.starttime AS start1, S1.endtime AS end1,
  S2.keycol AS key2, S2.starttime AS start2, S2.endtime AS end2
FROM dbo.Sessions AS S1
  JOIN dbo.Sessions AS S2
    ON S2.app = S1.app
    AND S2.usr = S1.usr
    AND (S2.endtime >= S1.starttime
         AND S2.starttime <= S1.endtime);

SELECT DISTINCT app, usr, starttime AS s
FROM dbo.Sessions AS O
WHERE NOT EXISTS
  (SELECT * FROM dbo.Sessions AS I
   WHERE I.app = O.app
     AND I.usr = O.usr
     AND O.starttime > I.starttime
     AND O.starttime <= I.endtime);

SELECT DISTINCT app, usr, endtime AS e
FROM dbo.Sessions AS O
WHERE NOT EXISTS
  (SELECT * FROM dbo.Sessions AS I
   WHERE I.app = O.app
     AND I.usr = O.usr
     AND O.endtime >= I.starttime
     AND O.endtime < I.endtime);

WITH StartTimes AS
(
  SELECT DISTINCT app, usr, starttime AS s
  FROM dbo.Sessions AS O
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS I
     WHERE I.app = O.app
       AND I.usr = O.usr
       AND O.starttime > I.starttime
       AND O.starttime <= I.endtime)
),
EndTimes AS
(
  SELECT DISTINCT app, usr, endtime AS e
  FROM dbo.Sessions AS O
  WHERE NOT EXISTS
    (SELECT * FROM dbo.Sessions AS I
     WHERE I.app = O.app
       AND I.usr = O.usr
       AND O.endtime >= I.starttime
       AND O.endtime < I.endtime)
),
SessionGroups AS
(
  SELECT app, usr, s,
   (SELECT MIN(e)
    FROM EndTimes AS EP
    WHERE EP.app = SP.app
      AND EP.usr = SP.usr
      AND e >= s) AS e
  FROM StartTimes AS SP
)
SELECT app, usr, s, e
FROM SessionGroups;

SELECT DISTINCT app, starttime AS ts
FROM dbo.Sessions;

SELECT app,
  (SELECT COUNT(*) FROM dbo.Sessions AS C
   WHERE ts >= starttime
     AND ts < endtime) AS cnt
FROM (SELECT DISTINCT app, starttime AS ts
     FROM dbo.Sessions) AS T;

SELECT app, MAX(cnt) AS mx
FROM (SELECT app,
        (SELECT COUNT(*) FROM dbo.Sessions AS C
         WHERE ts >= starttime
           AND ts < endtime) AS cnt
      FROM (SELECT DISTINCT app, starttime AS ts
           FROM dbo.Sessions) AS T) AS D
GROUP BY app;

Identifying the weekday of a given date is a much trickier problem than it might seem. Say, for example, that you were asked to return all orders from the Orders table in the Northwind database that were placed on a Tuesday. The DATEPART function using the weekday unit allows you to extract the weekday number (1 through 7) of a given DATETIME value. However, the weekday number that you will get for a given DATETIME value will vary depending on the setting of the session option DATEFIRST, which determines the first day of the week. If you set it to 1, you instruct SQL Server to consider Monday as the first day of the week, in which case you will filter orders with the weekday 2 for Tuesday. Setting DATEFIRST to 2 means that Tuesday will be considered the first day of the week, and so on.

If the DATEFIRST session option is not set explicitly, the session will set it implicitly based on your language settings. This is yet another example where you might not want to change a setting so that you avoid affecting other code in your session that depends on the current setting. Using the DATENAME to identify a certain weekday as an alternative does not solve the problem because the weekday name for a given DATETIME value can also vary based on your language settings. In short, you should look for an independent way to identify a weekday–one that is not based on settings or switches in the system.

The solution lies in logic. Think of the relationship between some value f (the DATEFIRST value) and the weekday number you will get back from the DATEPART function for a given DATETIME value d. The two values have an inverse relationship. That is, if you increase f by n, d is decreased by n. For example, if you set the DATEFIRST value to 1, meaning that Monday is the first day of the week. Given a DATETIME value that falls on a Tuesday, you will get 2 as the weekday number back from the DATEPART function. Now, increase the value of DATEFIRST by 1, setting it to 2, meaning that Tuesday is now the first day of the week. Now the weekday number that you will get back from the DATEPART function will be decreased by 1–namely, you will get 1 back. Keep in mind that the weekday numbers axis is cyclic. Cyclic nature will add a bit of complexity to the inverse relationship calculations. There’s a whole branch in mathematics that deals with cyclic axes. For example, here are a few calculations based on the weekday numbers axis: 1 + 1 = 2; 7 + 1 = 1; 7 – 1 = 6; 1 – 1 = 7.

Note that you have access to the session’s effective DATEFIRST value through the @@DATEFIRST value. Bearing this in mind, and the inverse relationship between the DATEFIRST setting and the weekday number you get back from the DATEPART function, here’s what you can do. Add @@DATEFIRST days to the given DATETIME value, and this way, you neutralize the effect of the DATEFIRST setting. Take the date ′20051004′ as an example, which happens to fall on Tuesday. Check the result of the expression after setting DATEFIRST to any value you want:

SELECT DATEPART(weekday, CAST('20051004' AS DATETIME) + @@DATEFIRST);

You will always get 3, as if you set DATEFIRST to 7. If you want to "logically" set DATEFIRST to 1, simply subtract the constant 1 from the date:

SELECT DATEPART(weekday, CAST('20051004' AS DATETIME) + @@DATEFIRST - 1);

If you do this, you will always get 2 for a Tuesday regardless of the DATEFIRST setting. To generalize the formula, you can get an independent weekday number for a given DATETIME value dt, as if DATEFIRST was logically set to n, by using the following expression:

DATEPART(weekday, dt + @@DATEFIRST - n)

Now we’ll deal with the original request. To get all orders placed on a Tuesday, assuming Monday as the first day of the week, use the following query:

USE Northwind;

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE DATEPART(weekday, OrderDate + @@DATEFIRST - 1) = 2;

The ability to calculate an independent weekday can come in handy when dealing with other problems that involve controlling which day of the week is considered first without changing the session’s DATEFIRST setting.

Steve Kass, the technical editor of the Inside T-SQL books, suggests the following neat solution:

The question "which orders were placed on a Tuesday?" can be answered simply with one easy-to-generalize idea. A date is a Tuesday means that the number of days between that date and another Tuesday is divisible by 7. For this kind of problem, a reference date is a valuable and general idea. It is handy to remember that January 1, 1900 was a Monday. Bearing this in mind, to return orders that were placed on a Tuesday, filter the rows where the difference in days between a reference date which is a Tuesday and the OrderDate modulo 7 is equal to zero, like so:

SELECT OrderID, OrderDate
FROM dbo.Orders
WHERE DATEDIFF(day, '19000102', OrderDate) % 7 = 0;

The problem that I will discuss in this section involves grouping data by the week. I will rely on the techniques I described earlier to calculate an independent weekday.

When you need to aggregate data based on DATETIME parts, usually the grouping elements can be easily derived from the original DATETIME value. However, when you need to group data by the week, the task is more challenging. To accomplish the task, suppose that you were asked to return the weekly count of orders. You don’t need to return weeks with no orders. If you use the DATEPART function with the week part, you will get back different week numbers for dates within a week that happens to span two years.

Instead of requesting the week number within the year, you can calculate a grouping factor for each order date. The grouping factor value must be the same for all orders within the same week and different from the value generated for orders placed on other weeks. An example of a grouping factor that fits these criteria is a common day within the input order date’s week–for example, the week start date or end date. Given an input order date, to return the start date of the week you simply need to subtract as many days as the input date’s weekday number and add one. Similarly, to return the end date of the week, you need to add 7 and subtract the weekday number.

Now that you know how to calculate an independent weekday number, you can group orders by the week, having full control over the first day of the week without changing the setting of DATEFIRST. Here’s the solution query, which returns the weekly count of orders shown in Table 1-10 (abbreviated):

SELECT od - wd + 1 AS week_start, od + 7 - wd AS week_end,
  COUNT(*) AS numorders
FROM (SELECT OrderID AS oid, OrderDate AS od,
        DATEPART(weekday, OrderDate + @@DATEFIRST - 1) AS wd
      FROM dbo.Orders) AS D
GROUP BY od - wd + 1, od + 7 - wd;

The derived table query simply calculates an independent weekday (wd) for each order date (od), assuming Monday as the first day of the week. The outer query then groups the data by week start (od – wd + 1) and week end (od + 7 – wd), returning the count of orders for each week.

Another approach to solve the problem is to rely on Steve’s reference date idea, using the following query:

DECLARE @RefDay AS DATETIME;
SET @RefDay = '19000101'; -- Monday

WITH NumWksAfter1900 AS
(
  SELECT
    DATEDIFF(day, @RefDay, OrderDate) / 7 AS weeks
  FROM dbo.Orders
),
WeekRanges AS
(
  SELECT
    DATEADD(day, 7 * weeks, @RefDay)     AS week_start,
    DATEADD(day, 7 * weeks, @RefDay) + 6 AS week_end
  FROM NumWksAfter1900
)
SELECT week_start, week_end, COUNT(*) AS numorders
FROM WeekRanges
GROUP BY week_start, week_end;

The code initializes the variable @RefDay with a reference date that falls on a day that you consider as the week start day (Monday, in our case). The CTE NumWksAfter1900 calculates, for each order, the number of whole weeks that passed since the reference date (weeks attribute). The CTE WeekRanges calculates the week boundaries for each weeks value from NumWksAfter1900; week_start is calculated by adding week * 7 days to the reference date, and week_end is calculated as the week start date plus 6 additional days. The outer query simply groups the data by the week boundaries, and returns the count of rows for each week.

The ISO week is a week-numbering standard by which a week number is not broken if the week spans two years. Week 1 of year Y is the week (Monday through Sunday) containing January 4 of year Y. To implement the ISO week-numbering standard, you need to consider two special cases: when January 1 through 3 belongs to the previous year, and when December 29 through 31 belongs to the next year. One might argue that it’s just as annoying to have the week number of January 1 be 53, or of December 30 to be 1 as the alternative. But it should be stressed that the ISO standard is a widely used international standard.

Run the code in Example 1-2 to create the ISOweek function implementation, which appears in Books Online.

IF OBJECT_ID (N'dbo.ISOweek', N'FN') IS NOT NULL
    DROP FUNCTION dbo.ISOweek;
GO
CREATE FUNCTION dbo.ISOweek (@DATE DATETIME)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
     DECLARE @ISOweek int
     SET @ISOweek= DATEPART(wk,@DATE)+1
          -DATEPART(wk,CAST(DATEPART(yy,@DATE) as CHAR(4))+'0104')
--Special case: Jan 1-3 might belong to the previous year
     IF (@ISOweek=0)
          SET @ISOweek=dbo.ISOweek(CAST(DATEPART(yy,@DATE)-1
               AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
--Special case: Dec 29-31 might belong to the next year
     IF ((DATEPART(mm,@DATE)=12) AND
          ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
          SET @ISOweek=1
     RETURN(@ISOweek)
END;
GO

Note that you need to set DATEFIRST to 1 before invoking the function. To test the function, run the following code, which invokes it against dates at the beginning and end of a year and returns the output shown in Table 1-11:

DECLARE @DF AS INT;
SET @DF = @@DATEFIRST;
SET DATEFIRST 1;

WITH Dates AS
(
  SELECT CAST('20050101' AS DATETIME) AS dt
  UNION ALL SELECT '20050102'
  UNION ALL SELECT '20050103'
  UNION ALL SELECT '20051231'
  UNION ALL SELECT '20060101'
  UNION ALL SELECT '20060102'
)
SELECT dt, dbo.ISOweek(dt) AS wk, DATENAME(weekday, dt) AS wd
FROM Dates;

SET DATEFIRST @DF;

Calculating the number of working days between two given dates is quite a common request. Note that both inclusive and non-inclusive counts are useful. In inclusive counts, I’m referring to taking into account the start and end dates of the range. I’ll be demonstrating techniques to calculate an inclusive count. In cases for which you need to consider weekends, holidays, and other special events as nonworking days, you might want to use an auxiliary table of dates. You mark each date as working or nonworking, and when requested to calculate the number of working days, you count the rows representing working days between the two given dates. You can even optimize the solution by keeping an attribute with a cumulative count of working days as of some base date. To calculate working days, simply retrieve the cumulative values of the given input dates and subtract one from another.

However, when you want to consider only weekends as nonworking days, you don’t need an auxiliary table at all. Instead, here’s a solution for calculating the number of working days between @s and @e, which can be local variables or input arguments of a routine:

DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = '20050101';
SET @e = '20051231';

SELECT
  days/7*5 + days%7
    - CASE WHEN 6 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
    - CASE WHEN 7 BETWEEN wd AND wd + days%7-1 THEN 1 ELSE 0 END
FROM (SELECT
        DATEDIFF(day, @s, @e) + 1 AS days,
        DATEPART(weekday, @s + @@DATEFIRST - 1) AS wd
     ) AS D;

The solution is very fast because it involves no I/O. The derived table query calculates the number of days (days) between @s and @e, inclusive of both @s and @e, and the weekday number (wd) of the date @s assuming Monday as the first day of the week. The outer query calculates the following: the number of working days in whole weeks covered by the range (days/7*5) plus the number of days in the partial week, if any (days%7), minus 1 if the partial week contains weekday 6 and minus 1 again if the partial week contains weekday 7. For the given dates–January 1, 2005 through December 31, 2005–you get 260 working days.

You might need a series of all possible dates between two input dates. Such a series could be used, for example, to populate a time dimension in Analysis Services. An auxiliary table of numbers makes the solution quite simple. Here’s the code to create the Nums table:

SET NOCOUNT ON;
USE tempdb;
GO

IF OBJECT_ID('dbo.Nums') IS NOT NULL
  DROP TABLE dbo.Nums;
GO
CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY);
DECLARE @max AS INT, @rc AS INT;
SET @max = 1000000;
SET @rc = 1;

INSERT INTO Nums VALUES(1);
WHILE @rc * 2 <= @max
BEGIN
  INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums;
  SET @rc = @rc * 2;
END

INSERT INTO dbo.Nums
  SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max;

I described the auxiliary table of numbers and the logic behind the code that populates it in Inside T-SQL Querying. Note that I’ll continue to refer to the Nums table throughout the book because it’s a very handy auxiliary table.

Here’s the code to generate the series of dates:

DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = '20060101';
SET @e = '20061231';

SELECT @s + n - 1 AS dt
FROM dbo.Nums
WHERE n <= DATEDIFF(day, @s, @e) + 1;

If you don’t have a Nums table and are not allowed to create new tables, you can use one of the table-valued function implementations that I showed in Inside T-SQL Querying. For example, I presented the User Defined Function (UDF) shown in Example 1-3, which accepts the desired number of rows as input and returns a sequence of numbers accordingly.

CREATE FUNCTION dbo.fn_nums(@n AS BIGINT) RETURNS TABLE
AS
RETURN
  WITH
  L0   AS(SELECT 1 AS c UNION ALL SELECT 1),
  L1   AS(SELECT 1 AS c FROM L0 AS A, L0 AS B),
  L2   AS(SELECT 1 AS c FROM L1 AS A, L1 AS B),
  L3   AS(SELECT 1 AS c FROM L2 AS A, L2 AS B),
  L4   AS(SELECT 1 AS c FROM L3 AS A, L3 AS B),
  L5   AS(SELECT 1 AS c FROM L4 AS A, L4 AS B),
  Nums AS(SELECT ROW_NUMBER() OVER(ORDER BY c) AS n FROM L5)
  SELECT n FROM Nums WHERE n <= @n;
GO

Once the function is created, you can use it just like you use the Nums table:

DECLARE @s AS DATETIME, @e AS DATETIME;
SET @s = '20060101';
SET @e = '20061231';

SELECT @s + n - 1 AS dt
FROM dbo.fn_nums(DATEDIFF(day, @s, @e) + 1) AS Nums;