Problem

You need to use the current date and time in your query.

Solution

Use the GETDATE, GETUTCDATE, CURRENT_TIMESTAMP, SYSDATETIME, SYSUTCDATETIME, or SYSDATETIMEOFFSET function to return the current time.

SELECT 'GETDATE()' AS [Function], GETDATE() AS [Value];
SELECT 'CURRENT_TIMESTAMP'AS [Function], CURRENT_TIMESTAMP AS [Value];
SELECT 'GETUTCDATE()' AS [Function], GETUTCDATE() AS [Value];
SELECT 'SYSDATETIME()' AS [Function], SYSDATETIME() AS [Value];
SELECT 'SYSUTCDATETIME()' AS [Function], SYSUTCDATETIME() AS [Value];
SELECT 'SYSDATETIMEOFFSET()' AS [Function], SYSDATETIMEOFFSET() AS [Value];

This query returns the following results (with the redundant headers omitted):

Function                Value
-------------------	----------------------------------
GETDATE()	        2012-02-10 21:27:20.070
CURRENT_TIMESTAMP	2012-02-10 21:27:20.070
GETUTCDATE()	        2012-02-11 02:27:20.070
SYSDATETIME()	        2012-02-10 21:27:20.0700370
SYSUTCDATETIME()	2012-02-11 02:27:20.0700370
SYSDATETIMEOFFSET()	2012-02-10 21:27:20.0700370 -05:00

How It Works

The GETDATE and CURRENT_TIMESTAMP functions return the local date and time, in a datetime data type. The GETUTCDATE function returns UTC time, also in a datetime data type. SYSDATETIME returns the local date and time in a datetime2 data type. SYSUTCDATETIME returns UTC time, also in a datetime2 data type. Finally, SYSDATETIMEOFFSET returns the local time, plus the number of hours and minutes offset from UTC, in a datetimeoffset data type.

Problem

You need to convert a date/time value from one time zone to another.

Solution

Use the SWITCHOFFSET function to convert date/time values in one time zone to a different time zone.

SELECT SWITCHOFFSET('2007-08-12T09:43:25-05:00', '+03:00'),

This query returns the following result:

2007-08-12 17:43:25.0000000 +03:00

How It Works

The SWITCHOFFSET function converts a datetimeoffset value (or a value that can be implicitly converted to a datetimeoffset value) to a different time zone, adjusting the date, hours, and minutes as necessary. The returned value will be the same UTC time as the supplied value.

Problem

You need to convert a date/time value to a datetimeoffset value for use in the SWITCHOFFSET function.

Solution

Use the TODATETIMEOFFSET function. This example converts the system's current date/time value to the current time in the Eastern Time Zone (without DST adjustments) and displays both that time and the current system time in a datetimeoffset format.

SELECT TODATETIMEOFFSET(GETDATE(), '-05:00') AS [Eastern Time Zone Time],
       SYSDATETIMEOFFSET() [Current System Time];

This query returns the following result:

Eastern Time Zone Time          Current System Time
------------------------------	----------------------------------
2012-02-13 21:33:36.517 -05:00	2012-02-13 21:33:36.5200068 -05:00

How It Works

The TODATETIMEOFFSET function converts a datetime2 value (or a value that can be implicitly converted into a datetime2 value) to a datetimeoffset value of the specified time zone.

Problem

You need to add an interval to a date or time portion of a date/time value.

Solution

Use the DATEADD function to add any quantity of any portion of a date or time value.

SELECT DATEADD(YEAR, -1, '2009-04-02T00:00:00'),

This query returns the following result:

2008-04-02 00:00:00.000

How It Works

The DATEADD function has three parameters. The first parameter is the part of the date to modify, and it can be any of the names or abbreviations shown in Table 10-2.

The second parameter is a numeric value for the number of datepart units that you are adding to the date. If the value is negative, these units will be subtracted from the date. Finally, the third parameter is the date being modified.

Problem

You need to calculate the difference between two dates.

Solution

Use the DATEDIFF function to calculate the difference between any two dates.

SELECT TOP (5)
       ProductID,
       GETDATE() AS Today,
       EndDate,
       DATEDIFF(MONTH, EndDate, GETDATE()) AS ElapsedMonths
FROM Production.ProductCostHistory
WHERE EndDate IS NOT NULL;

This query returns the ProductID, the current date/time, the product's EndDate, and the number of months between the EndDate and today's date. The first five records in this table are as follows:

ProductID	Today EndDate	ElapsedMonths
---------	-----------------------	-----------------------	-------------
707	2012-02-11 08:16:08.663	2006-06-30 00:00:00.000	68
707	2012-02-11 08:16:08.663	2007-06-30 00:00:00.000	56
708	2012-02-11 08:16:08.663	2006-06-30 00:00:00.000	68
708	2012-02-11 08:16:08.663	2007-06-30 00:00:00.000	56
709	2012-02-11 08:16:08.663	2006-06-30 00:00:00.000	68

How It Works

The DATEDIFF function accepts three parameters; the first is the datepart (from Table 10-2) to identify whether you are counting the difference in terms of days, hours, minutes, months etc. The last two parameters are the two dates you want to compare.

Notice that the DATEDIFF function returns the number of datepart boundaries crossed; this is not the same as the elapsed time between the two dates. For instance, for the following query, each column returns the quantity of one datepart boundary crossed for each of the specified dateparts, even though the difference between these two date/time values is 100 nanoseconds (.000001 seconds).

WITH cteDates (StartDate, EndDate) AS
(
SELECT CONVERT(DATETIME2, '2010-12-31T23:59:59.9999999'),
       CONVERT(DATETIME2, '2011-01-01T00:00:00.0000000')
)
SELECT StartDate,
       EndDate,
       DATEDIFF(YEAR, StartDate, EndDate) AS Years,
       DATEDIFF(QUARTER, StartDate, EndDate) AS Quarters,
       DATEDIFF(MONTH, StartDate, EndDate) AS Months,
       DATEDIFF(DAY, StartDate, EndDate) AS Days,
       DATEDIFF(HOUR, StartDate, EndDate) AS Hours,
       DATEDIFF(MINUTE, StartDate, EndDate) AS Minutes,
       DATEDIFF(SECOND, StartDate, EndDate) AS Seconds,
       DATEDIFF(MILLISECOND, StartDate, EndDate) AS Milliseconds,
       DATEDIFF(MICROSECOND, StartDate, EndDate) AS MicroSeconds
FROM cteDates;

Problem

You need to find the elapsed time between two dates.

Solution

You need to calculate the number of datepart boundaries crossed at the smallest precision level that you are interested in and then calculate the higher datepart boundaries from that number. For example, the following code determines the elapsed time down to the seconds:

DECLARE @StartDate DATETIME2 = '2012-01-01T18:25:42.9999999',
        @EndDate DATETIME2 = '2012-06-15T13:12:11.8675309';
WITH cte AS
(
SELECT DATEDIFF(SECOND, @StartDate, @EndDate) AS ElapsedSeconds,
       DATEDIFF(SECOND, @StartDate, @EndDate)/60 AS ElapsedMinutes,
       DATEDIFF(SECOND, @StartDate, @EndDate)/3600 AS ElapsedHours,
       DATEDIFF(SECOND, @StartDate, @EndDate)/86400 AS ElapsedDays
)
SELECT @StartDate AS StartDate,
       @EndDate AS EndDate,
       CONVERT(VARCHAR(10), ElapsedDays) + ':' +
        CONVERT(VARCHAR(10), ElapsedHours%24) + ':' +
        CONVERT(VARCHAR(10), ElapsedMinutes%60) + ':' +
        CONVERT(VARCHAR(10), ElapsedSeconds%60) AS [ElapsedTime (D:H:M:S)]
FROM cte;

This query returns the following result:

StartDate	EndDate	ElapsedTime (D:H:M:S)
---------------------------	---------------------------	---------------------
2012-01-01 18:25:42.9999999	2012-06-15 13:12:11.8675309	165:18:46:29

How It Works

Since we are interested in knowing the elapsed time down to the second, we start off by getting the number of SECOND datepart boundaries that are crossed between these two dates. There are 60 seconds in a minute, so we then take the number of seconds and divide by 60 to get the number of minutes. There are 3,600 seconds in an hour (60 x 60), so we then divide the number of seconds by 3,600 to get the number of hours. And there are 86,400 seconds in a day (60 x 60 x 24), so we divide the number of seconds by 86,400 to get the number of hours.

However, these are not quite the numbers we are looking for; we need to express this as the number of that particular datepart boundary after the next highest boundary, for example the number of hours past the number of whole days. So, we then use the modulo operator to get the remaining number of hours that don't make up an entire day (Hours modulo 24), the remaining number of minutes that don't make up an entire hour (Minutes modulo 60), and the remaining number of seconds that don't make up an entire minute (Seconds modulo 60). Since all of these divisions are occurring with an integer, the fractional remainder will be truncated, so we do not have to worry about this floating down to the next lower datepart boundary calculation.

You can easily adapt this method for a finer precision (milliseconds, and so on). However, to get a less fine precision (for example, years), you now need to start looking at whether a year is a leap year, so you will need to be applying leap year criteria to your calculation.

Problem

You need to return the name of the month and day of the week for a specific date.

Solution

Use the DATENAME function to get the name of the datepart portion of the date.

SELECT TOP (5)
       ProductID,
       EndDate,
       DATENAME(MONTH, EndDate) AS MonthName,
       DATENAME(WEEKDAY, EndDate) AS WeekDayName
FROM Production.ProductCostHistory
WHERE EndDate IS NOT NULL;

This query returns the following results:

ProductID	EndDate	MonthName	WeekDayName
---------	-----------------------	---------	-----------
707	2006-06-30 00:00:00.000	June	Friday
707	2007-06-30 00:00:00.000	June	Saturday
708	2006-06-30 00:00:00.000	June	Friday
708	2007-06-30 00:00:00.000	June	Saturday
709	2006-06-30 00:00:00.000	June	Friday

How It Works

The DATENAME function returns a character string representing the datepart specified. While any of the dateparts listed in Table 10-2 can be used, only the month and weekday dateparts convert to a name; the other dateparts return the value as a string.

Problem

You need to separate a date into different columns for year, month, and date.

Solution

Use the DATEPART function to retrieve the datepart specified from a date as an integer.

SELECT TOP (5)
       ProductID,
       EndDate,
       DATEPART(YEAR, EndDate) AS [Year],
       DATEPART(MONTH, EndDate) AS [Month],
       DATEPART(DAY, EndDate) AS [Day]
FROM Production.ProductCostHistory
WHERE EndDate IS NOT NULL;

This query returns the following results:

ProductID	EndDate	Year	Month	Day
---------	-----------------------	----	-----	---
707	2006-06-30 00:00:00.000	2006	6	30
707	2007-06-30 00:00:00.000	2007	6	30
708	2006-06-30 00:00:00.000	2006	6	30
708	2007-06-30 00:00:00.000	2007	6	30
709	2006-06-30 00:00:00.000	2006	6	30

How It Works

The DATEPART function retrieves the specified datepart from the date as an integer. Any of the dateparts in Table 10-2 can be utilized.

Problem

You need to determine whether the value of a string is a valid date.

Solution

You need to utilize the ISDATE function in your query.

SELECT MyData ,
       ISDATE(MyData) AS IsADate
FROM (VALUES('IsThisADate'),
        ('2012-02-14'),
        ('2012-01-01T00:00:00'),
        ('2012-12-31T23:59:59.9999999')) dt(MyData);

This query returns the following results:

MyData	IsADate
---------------------------	-------
IsThisADate	0
2012-02-14	1
2012-01-01T00:00:00	1
2012-12-31T23:59:59.9999999	0

How It Works

The ISDATE function checks to see whether the expression passed to it is a valid date, time, or datetime value. If the expression is a valid date, a true (1) will be returned; otherwise, a false (0) will be returned. Because the last record is a datetime2 data type, it does not pass this check.

Problem

You need to determine what the last day of the month is for a date you are working with.

Solution

Use the EOMONTH function to determine the last day of the month for a given date.

SELECT MyData,
       EOMONTH(MyData) AS LastDayOfThisMonth,
       EOMONTH(MyData, 1) AS LastDayOfNextMonth
FROM (VALUES ('2012-02-14T00:00:00' ),
        ('2012-01-01T00:00:00'),
        ('2012-12-31T23:59:59.9999999')) dt(MyData);

This query returns the following results:

MyData	LastDayOfThisMonth	LastDayOfNextMonth
---------------------------	------------------	------------------
2012-02-14T00:00:00	2012-02-29	2012-03-31
2012-01-01T00:00:00	2012-01-31	2012-02-29
2012-12-31T23:59:59.9999999	2012-12-31	2013-01-31

How It Works

The EOMONTH function returns the last day of the month for the specified date. It has an optional parameter that will add the specified number of months to the specified date.

Problem

You need to create a date from numbers representing the various parts of the date. For example, you have data for the year, month, and day parts of a day, and you need to make a date out of those numbers.

Solution

Use the DATEFROMPARTS function to build a date from the numbers representing the year, month, and day.

SELECT 'DateFromParts' AS ConversionType,
       DATEFROMPARTS(2012, 8, 15) AS [Value];
SELECT 'TimeFromParts' AS ConversionType,
       TIMEFROMPARTS(18, 25, 32, 5, 1) AS [Value];
SELECT 'SmallDateTimeFromParts' AS ConversionType,
       SMALLDATETIMEFROMPARTS(2012, 8, 15, 18, 25) AS [Value];
SELECT 'DateTimeFromParts' AS ConversionType,
       DATETIMEFROMPARTS(2012, 8, 15, 18, 25, 32, 450) AS [Value];
SELECT 'DateTime2FromParts' AS ConversionType,
       DATETIME2FROMPARTS(2012, 8, 15, 18, 25, 32, 5, 7) AS [Value];
SELECT 'DateTimeOffsetFromParts' AS ConversionType,
       DATETIMEOFFSETFROMPARTS(2012, 8, 15, 18, 25, 32, 5, 4, 0, 7) AS [Value];

This query returns the following result set (with redundant headers removed):

ConversionType	Value
--------------	----------
DateFromParts	2012-08-15
TimeFromParts	18:25:32.5
SmallDateTimeFromParts	2012-08-15 18:25:00
DateTimeFromParts	2012-08-15 18:25:32.450
DateTime2FromParts	2012-08-15 18:25:32.0000005
DateTimeOffsetFromParts	2012-08-15 18:25:32.0000005 +04:00

How It Works

The functions demonstrated earlier build an appropriate date/time value in the specified data type from the parts that make up that data type.

The TIMEFROMPARTS, DATETIME2FROMPARTS, and DATETIMEOFFSETFROMPARTS functions have a fraction parameter and a precision parameter. For the latter two, the fraction is the seventh parameter (in the previous example, the 5), and the precision parameter is the last parameter (the 7). For the TIMEFROMPARTS function, these parameters are the last two parameters. These parameters work together to control what precision the fraction is applied to. This is best demonstrated with the following query:

SELECT TIMEFROMPARTS(18, 25, 32, 5, 1);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 2);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 3);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 4);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 5);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 6);
SELECT TIMEFROMPARTS(18, 25, 32, 5, 7);
SELECT TIMEFROMPARTS(18, 25, 32, 50, 2);
SELECT TIMEFROMPARTS(18, 25, 32, 500, 3);

These queries return the following result set (with the header lines removed):

18:25:32.5
18:25:32.05
18:25:32.005
18:25:32.0005
18:25:32.00005
18:25:32.000005
18:25:32.0000005
18:25:32.50
18:25:32.500

Problem

You need to determine what the first day of a datepart boundary is for a specified date. For example, you want to know what the first day of the current quarter is for the specified date.

Solution #1

Use the DATEADD and DATEDIFF functions to perform this calculation.

DECLARE @MyDate DATETIME2 = '2012-01-01T18:25:42.9999999',
        @Base DATETIME = '1900-01-01T00:00:00',
        @Base2 DATETIME = '2000-01-01T00:00:00';

-- Solution 1
SELECT MyDate,
        DATEADD(YEAR, DATEDIFF(YEAR, @Base, MyDate), @Base) AS [FirstDayOfYear],
        DATEADD(MONTH, DATEDIFF(MONTH, @Base, MyDate), @Base) AS [FirstDayOfMonth],
        DATEADD(QUARTER,DATEDIFF(QUARTER, @Base, MyDate), @Base) AS [FirstDayOfQuarter]
FROM (VALUES ('1981-01-17T00:00:00'),
        ('1961-11-23T00:00:00'),
        ('1960-07-09T00:00:00'),
        ('1980-07-11T00:00:00'),
        ('1983-01-05T00:00:00'),
        ('2006-11-27T00:00:00')) dt (MyDate);

SELECT 'StartOfHour' AS ConversionType,
       DATEADD(HOUR, DATEDIFF(HOUR, @Base, @MyDate), @Base) AS DateResult
UNION ALL
SELECT 'StartOfMinute',
       DATEADD(MINUTE, DATEDIFF(MINUTE, @Base, @MyDate), @Base)
UNION ALL
SELECT 'StartOfSecond',
       DATEADD(SECOND, DATEDIFF(SECOND, @Base2, @MyDate), @Base2);

This query returns the following:

MyDate	FirstDayOfYear	FirstDayOfMonth	FirstDayOfQuarter
-------------------	-----------------------	-----------------------	-----------------------
1981-01-17T00:00:00	1981-01-01 00:00:00.000	1981-01-01 00:00:00.000	1981-01-01 00:00:00.000
1961-11-23T00:00:00	1961-01-01 00:00:00.000	1961-11-01 00:00:00.000	1961-10-01 00:00:00.000
1960-07-09T00:00:00	1960-01-01 00:00:00.000	1960-07-01 00:00:00.000	1960-07-01 00:00:00.000
1980-07-11T00:00:00	1980-01-01 00:00:00.000	1980-07-01 00:00:00.000	1980-07-01 00:00:00.000
1983-01-05T00:00:00	1983-01-01 00:00:00.000	1983-01-01 00:00:00.000	1983-01-01 00:00:00.000
2006-11-27T00:00:00	2006-01-01 00:00:00.000	2006-11-01 00:00:00.000	2006-10-01 00:00:00.000

ConversionType	DateResult
--------------	-----------------------
StartOfHour	2012-01-01 18:00:00.000
StartOfMinute	2012-01-01 18:25:00.000
StartOfSecond	2012-01-01 18:25:42.000

Solution #2

Break the date down into the appropriate parts, and then use the DATETIMEFROMPARTS function to build a new date with the parts being truncated set to 1 (for months/dates) or zero (for hours/minutes/seconds/milliseconds).

SELECT MyDate,
       DATETIMEFROMPARTS(ca.Yr, 1, 1, 0, 0, 0, 0) AS FirstDayOfYear,
       DATETIMEFROMPARTS(ca.Yr, ca.Mn, 1, 0, 0, 0, 0) AS FirstDayOfMonth,
       DATETIMEFROMPARTS(ca.Yr, ca.Qt, 1, 0, 0, 0, 0) AS FirstDayOfQuarter
FROM (VALUES ('1981-01-17T00:00:00'),
        ('1961-11-23T00:00:00'),
        ('1960-07-09T00:00:00'),
        ('1980-07-11T00:00:00'),
        ('1983-01-05T00:00:00'),
        ('2006-11-27T00:00:00')) dt (MyDate)
CROSS APPLY (SELECT DATEPART(YEAR, dt.MyDate) AS Yr,
        DATEPART(MONTH, dt.MyDate) AS Mn,
        ((CEILING(MONTH(dt.MyDate)/3.0)*3)-2) AS Qt
        ) ca;

WITH cte AS
(
SELECT DATEPART(YEAR, @MyDate) AS Yr,
       DATEPART(MONTH, @MyDate) AS Mth,
       DATEPART(DAY, @MyDate) AS Dy,
       DATEPART(HOUR, @MyDate) AS Hr,
       DATEPART(MINUTE, @MyDate) AS Mn,
       DATEPART(SECOND, @MyDate) AS Sec
)
SELECT 'StartOfHour' AS ConversionType,
       DATETIMEFROMPARTS(cte.Yr, cte.Mth, cte.Dy, cte.Hr, 0, 0, 0) AS DateResult
FROM cte
UNION ALL
SELECT 'StartOfMinute',
       DATETIMEFROMPARTS(cte.Yr, cte.Mth, cte.Dy, cte.Hr, cte.Mn, 0, 0)
FROM cte
UNION ALL
SELECT 'StartOfSecond',
       DATETIMEFROMPARTS(cte.Yr, cte.Mth, cte.Dy, cte.Hr, cte.Mn, cte.Sec, 0)
FROM cte;

Solution #3

Use the FORMAT function to format the date, using default values for the parts to be truncated.

SELECT CONVERT(CHAR(10), ca.MyDate, 121) AS MyDate,
       CAST(FORMAT(ca.MyDate, 'yyyy-01-01') AS DATETIME) AS FirstDayOfYear,
       CAST(FORMAT(ca.MyDate, 'yyyy-MM-01') AS DATETIME) AS FirstDayOfMonth
FROM (VALUES ('1981-01-17T00:00:00'),
        ('1961-11-23T00:00:00'),
        ('1960-07-09T00:00:00'),
        ('1980-07-11T00:00:00'),
        ('1983-01-05T00:00:00'),
        ('2006-11-27T00:00:00')) dt (MyDate)
CROSS APPLY (SELECT CAST(dt.MyDate AS DATE)) AS ca(MyDate);

SELECT 'StartOfHour' AS ConversionType,
       FORMAT(@MyDate, 'yyyy-MM-dd HH:00:00.000') AS DateResult
UNION ALL
SELECT 'StartOfMinute',
       FORMAT(@MyDate, 'yyyy-MM-dd HH:mm:00.000')
UNION ALL
SELECT 'StartOfSecond',
       FORMAT(@MyDate, 'yyyy-MM-dd HH:mm:ss.000'),

How It Works #1

In the first solution, for the datepart boundary that you are interested in, you use the DATEDIFF function to return the number of boundaries between a known date and the date that you are comparing to. You then use the DATEADD function to add this number of boundaries back to the known date. Remember that the DATEDIFF returns an integer; choose your known date so that you won't cause a numeric overflow. This can become problematic when you work with the SECOND datepart boundary (or one of the fractional second datepart boundaries).

How It Works #2

In the second solution, the year, month, and beginning month of the quarter are calculated in the CROSS APPLY operator. The parts to keep are passed in to the DATETIMETOPARTS function, and default values are passed in for the remaining parts to generate the desired dates. For the second part of this solution, the year, month, day, hour, minute, and second parts are extracted, and then the desired parts are passed in to the DATETIMETOPARTS function, with the parts of the time to be truncated set to zero. This solution produces the same results as Solution #1.

How It Works #3

In the third solution, the FORMAT function utilizes the .NET 4.0 formatting capabilities to format the date as a string, and then the CAST function is utilized to change the string back into a datetime data type. The parts of the time that are to be truncated are set to zero, while for the first day of calculations, the day and month are set to 1 where appropriate. With the exception of FirstDayOfQuarter, this solution returns the same results as Solution #1.

Problem

You are producing a report that breaks down expenses by category and that sums up the expenses at the month level. One of your categories does not have expenses for every month, so those months are missing values in the report. You want those missing months to be reported with a value of zero for the expense amount.

Solution

Utilize a calendar table to generate the missing months.

DECLARE @Base DATETIME = '1900-01-01T00:00:00';
WITH cteExpenses AS
(
SELECT ca.FirstOfMonth,
        SUM(ExpenseAmount) AS MonthlyExpenses
FROM ( VALUES ('2012-01-15T00:00:00', 1250.00),
        ('2012-01-28T00:00:00', 750.00),
        ('2012-03-01T00:00:00', 1475.00),
        ('2012-03-23T00:00:00', 2285.00),
        ('2012-04-01T00:00:00', 1650.00),
        ('2012-04-22T00:00:00', 1452.00),
        ('2012-06-15T00:00:00', 1875.00),
        ('2012-07-23T00:00:00', 2125.00) ) dt (ExpenseDate, ExpenseAmount)
        CROSS APPLY (SELECT DATEADD(MONTH,
        DATEDIFF(MONTH, @Base, ExpenseDate), @Base) ) ca (FirstOfMonth)
GROUP BY ca.FirstOfMonth
), cteMonths AS
(
SELECT DATEFROMPARTS(2012, M, 1) AS FirstOfMonth
FROM ( VALUES (1), (2), (3), (4),
        (5), (6), (7), (8),
        (9), (10), (11), (12) ) Months (M)
)
SELECT CAST(FirstOfMonth AS DATE) AS FirstOfMonth,
       MonthlyExpenses
FROM cteExpenses
UNION ALL
SELECT m.FirstOfMonth,
       0
FROM cteMonths M
       LEFT JOIN cteExpenses e
        ON M.FirstOfMonth = e.FirstOfMonth
WHERE e.FirstOfMonth IS NULL
ORDER BY FirstOfMonth;

This query produces the following results:

FirstOfMonth	MonthlyExpenses
------------	---------------
2012-01-01	2000.00
2012-02-01	0.00
2012-03-01	3760.00
2012-04-01	3102.00
2012-05-01	0.00
2012-06-01	1875.00
2012-07-01	2125.00
2012-08-01	0.00
2012-09-01	0.00
2012-10-01	0.00
2012-11-01	0.00
2012-12-01	0.00

How It Works

The cteExpenses common table expression builds a derived table of expense dates and amounts. The CROSS APPLY operator converts each date to the date at the beginning of the month. The expenses are then summed up and grouped by the beginning of the month date. If we run just this portion of the query, we get the following results:

FirstOfMonth	MonthlyExpenses
-----------------------	---------------
2012-01-01 00:00:00.000	2000.00
2012-03-01 00:00:00.000	3760.00
2012-04-01 00:00:00.000	3102.00
2012-06-01 00:00:00.000	1875.00
2012-07-01 00:00:00.000	2125.00

As you can see, several months are missing. To include these missing months, the cteMonths common table expression is created, which uses the DATEFROMPARTS function to build the first day of the month for each month. Running just this portion of the query returns the following results:

FirstOfMonth
------------
2012-01-01
2012-02-01
2012-03-01
2012-04-01
2012-05-01
2012-06-01
2012-07-01
2012-08-01
2012-09-01
2012-10-01
2012-11-01
2012-12-01

Finally, the expenses are returned, and this result set is unioned to a second result set that returns the months left-joined to the expenses and is filtered to return only the months that do not exist in the expenses. The result is that all months are shown in the result set, with the months without data having a zero value.

In this recipe, a virtual calendar table was created that contains the first day of each month in the year. Frequently, calendar tables will contain days for every day in the year, with additional columns to hold other information such as the first day of the month, the day of the week for the date, and whether this is a weekday or a weekend date or a holiday. Using a prebuilt calendar table can greatly simplify many calculations that would need to be performed.

Problem

You need to find the date of an arbitrary date, such as the third Thursday in November or the date for last Friday.

Solution

Use a calendar table with additional columns to query the desired dates.

CREATE TABLE dbo.Calendar (
  [Date] DATE CONSTRAINT PK_Calendar PRIMARY KEY CLUSTERED,
  FirstDayOfYear DATE,
  LastDayOfYear DATE,
  FirstDayOfMonth DATE,
  LastDayOfMonth DATE,
  FirstDayOfWeek DATE,
  LastDayOfWeek DATE,
  DayOfWeekName NVARCHAR(20),
  IsWeekDay BIT,
  IsWeekEnd BIT);
GO
DECLARE @Base DATETIME = '1900-01-01T00:00:00',
        @Start DATETIME = '2000-01-01T00:00:00'
INSERT INTO dbo.Calendar
SELECT TOP 9497
       ca.Date,
       cy.FirstDayOfYear,
       cyl.LastDayOfYear,
       cm.FirstDayOfMonth,
       cml.LastDayOfMonth,
       cw.FirstDayOfWeek,
       cwl.LastDayOfWeek,
       cd.DayOfWeekName,
       cwd.IsWeekDay,
       CAST(cwd.IsWeekDay - 1 AS BIT) AS IsWeekEnd
FROM (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0))
        FROM sys.all_columns t1
        CROSS JOIN sys.all_columns t2) dt (RN)
CROSS APPLY (SELECT DATEADD(DAY, RN-1, @Start)) AS ca(Date)
CROSS APPLY (SELECT DATEADD(YEAR, DATEDIFF(YEAR, @Base, ca.Date), @Base)) AS cy(FirstDayOfYear)
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(YEAR, 1, cy.FirstDayOfYear))) AS cyl(LastDayOfYear)
CROSS APPLY (SELECT DATEADD(MONTH, DATEDIFF(MONTH, @Base, ca.Date), @Base)) AS cm(FirstDayOfMonth)
CROSS APPLY (SELECT DATEADD(DAY, -1, DATEADD(MONTH, 1, cm.FirstDayOfMonth))) AS cml(LastDayOfMonth)
CROSS APPLY (SELECT DATEADD(DAY,-(DATEPART(weekday ,ca.Date)-1),ca.Date)) AS cw(FirstDayOfWeek)
CROSS APPLY (SELECT DATEADD(DAY, 6, cw.FirstDayOfWeek)) AS cwl(LastDayOfWeek)
CROSS APPLY (SELECT DATENAME(weekday, ca.Date)) AS cd(DayOfWeekName)
CROSS APPLY (SELECT CASE WHEN cd.DayOfWeekName IN ('Monday', 'Tuesday', 'Wednesday',
        'Thursday', 'Friday')
        THEN 1
        ELSE 0
        END) AS cwd(IsWeekDay);
GO

WITH cte AS
(
SELECT FirstDayOfMonth,
        Date,
        RN = ROW_NUMBER() OVER (PARTITION BY FirstDayOfMonth ORDER BY Date)
FROM dbo.Calendar
WHERE DayOfWeekName = 'Thursday'
)
SELECT Date
FROM cte
WHERE RN = 3
AND FirstDayOfMonth = '2012-11-01T00:00:00';

SELECT c1.Date
FROM dbo.Calendar c1 -- prior week
        JOIN dbo.Calendar c2 -- current week
        ON c1.FirstDayOfWeek = DATEADD(DAY, -7, c2.FirstDayOfWeek)
WHERE c1.DayOfWeekName = 'Friday'
AND c2.Date = CAST(GETDATE() AS DATE);

This query returns the following result sets:

Date
----------
2012-11-15

Date
----------
2012-04-27

How It Works

This recipe creates a calendar table with columns for extra information—the first and last days of the year, the month and week, the weekday name for the date, and whether this date is a weekday or weekend. This table is then populated for all the dates between January 1, 2000, and December 31, 2025.

The first date that is retrieved is the third Thursday in November. The query gets all of the Thursdays, along with the first day of the month. It then calculates a row number for that date in that month. Finally, the date for the third Thursday in November is returned.

The second date that is retrieved is the Friday of the previous week. The query starts off by performing a self-join to the calendar table. On the current week side of the join, the first day of the week for today's date is obtained and used to join to the previous week by subtracting seven days to get the first day of the previous week. It then returns the date for that week that has a weekday name of Friday.

As you can see, calendar tables can be easily adjusted to suit your needs. A column of IsWorkingDay could be added and populated, and then it would be easy to find the date five working days in the future. Holidays can be tracked, as can fiscal accounting periods, especially those that don't follow the norm of the calendar year.

Calendar tables are typically sized to hold several years worth of data. It takes less than 10,000 records to hold every date between January 1, 2000, and December 31, 2025. Because of its small size and static nature, this is one of those tables that benefits from being heavily indexed to provide covering indexes for all the queries you would run against it.

Problem

You want to count the number of employees that were employed during each month.

Solution

Use a calendar table to get the months employees were active and aggregate the data.

WITH cte AS
(
SELECT edh.BusinessEntityID,
        c.FirstDayOfMonth
FROM AdventureWorks2012.HumanResources.EmployeeDepartmentHistory AS edh
        JOIN dbo.Calendar AS c
        ON c.Date BETWEEN edh.StartDate
        AND ISNULL(edh.EndDate, GETDATE())
GROUP BY edh.BusinessEntityID,
        c.FirstDayOfMonth
)
SELECT FirstDayOfMonth,
        COUNT(*) AS EmployeeQty
FROM cte
GROUP BY FirstDayOfMonth
ORDER BY FirstDayOfMonth;

This query returns the following (abridged) result set:

FirstDayOfMonth	EmployeeQty
---------------	-----------
2000-07-01	1
2000-08-01	1
2000-09-01	1
2000-10-01	1
2000-11-01	1
…
2007-03-01	287
2007-04-01	288
2007-05-01	288
2007-06-01	288
2007-07-01	290
2007-08-01	290
2007-09-01	290

How It Works

Using the 25-year calendar table created in Recipe 10-14, the beginning of the month and employee identifier are returned for each employee who is active between the start date and end date (or current date if null). The GROUP BY clause is utilized to eliminate duplicates created by each date within a month. The employees are then counted per month.

Problem

When exchanging data with a company in a different country, dates either are converted incorrectly or generate an error when attempting to import.

Solution

Use one of the ISO-8601 date formats to ensure that the date/time value is unambiguous.

SELECT 'sysdatetime' AS ConversionType, 126 AS Style,
       CONVERT(varchar(30), SYSDATETIME(), 126) AS [Value] UNION ALL
SELECT 'sysdatetime', 127,
       CONVERT(varchar(30), SYSDATETIME(), 127) UNION ALL
SELECT 'getdate', 126,
       CONVERT(varchar(30), GETDATE(), 126) UNION ALL
SELECT 'getdate', 127,
       CONVERT(varchar(30), GETDATE(), 127);

This code returns the following result set:

ConversionType	Style	Value
--------------	-----	---------------------------
sysdatetime	126	2012-03-17T01:57:43.4321425
sysdatetime	127	2012-03-17T01:57:43.4321425
getdate	126	2012-03-17T01:57:43.430
getdate	127	2012-03-17T01:57:43.430

How It Works

When working with dates across national boundaries, you frequently run into data conversion issues. For instance, take 02/04/2012: in the United States, this is February 4, 2012, while in the United Kingdom, this is April 2, 2012. In this example, the date is converted improperly. Another example is 12/25/2012: in the United States, this is December 25, 2012; in the United Kingdom, this would be attempted to be converted into the 12th day of the 25th month of 2012, and since there aren't 25 months, this would generate an error.

Any date with the month and day values both being less than or equal to 12 is ambiguous (unless the values are the same). Any date with a number greater than 12 may generate an error when attempting to convert it to a date data type.

To work with this date conversion issue, an international standard was created. This standard is ISO-8601: “Data elements and interchange formats – Information interchange – Representation of dates and times.” There are two formats that are allowed to represent date with time:

YYYY-MM-DDThh:mm:ss[.nnnnnnn][{+|-}hh:mm]
YYYY-MM-DDThh:mm:ss[.nnnnnnn]Z (UTC, Coordinated Universal Time)

The following are examples of using these formats:

2012-07-28T16:45:33
2012-07-28T16:45:33.1234567+07:00
2012-07-28T16:45:33.1234567Z

To properly use the ISO-8601 format, the date and time portions must be specified, including the separators, meaning the T, the colons (:), the + or – , and the periods (.). The brackets indicate that the fractional seconds and time zone offset portions of the time are optional. The time is specified using the 24-hour clock format. The T is used to indicate the start of the time portion of the date-time value in the string. The Z indicates the time is in UTC time.

The date/time values that are specified in this format are unambiguous. The SET DATEFORMAT and SET LANGUAGE login default language settings do not affect the results.

When querying dates and times, the CONVERT function has two styles (126 and 127) that convert a date/time data type into the ISO-8601 formatted date with time values.

In this chapter, and throughout this book, examples with dates use the ISO-8601 standard.