Working with Dates and Times
SQL Server has several different date and time data types, which have varying levels of range and precision (and corresponding varying levels of storage requirement space). SQL Server also has numerous functions to retrieve, modify, and validate the data from these data types in their various formats. This chapter focuses on these functions. Table 10-1 shows the various date/time data types.
Table 10-1. SQL Server Date/Time Data Types
10-1. Returning the Current Date and Time
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() 2015-01-23 23:47:39.170
CURRENT_TIMESTAMP 2015-01-23 23:47:39.170
GETUTCDATE() 2015-01-24 04:47:39.170
SYSDATETIME() 2015-01-23 23:47:39.1728701
SYSUTCDATETIME() 2015-01-24 04:47:39.1728701
SYSDATETIMEOFFSET() 2015-01-23 23:47:39.1728701 -05:00
Note Many of the recipes in this chapter call one or more functions that return a value based upon the current date and time. When you run these recipes on your system, you will get a different result that will be based upon the date and time as set on the computer running your instance of SQL Server.
How It Works
The GETDATE and CURRENT_TIMESTAMP functions return the local date and time in a date/time data type. The GETUTCDATE function returns UTC time, also in a date/time 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.
10-2. Converting Between Time Zones
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 corresponding values in a different time zone.
SELECT SWITCHOFFSET(SYSDATETIMEOFFSET(), '+03:00'),
This query returns the following result:
2015-01-24 07:50:54.0050138 +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.
Note The SWITCHOFFSET function is not aware of daylight saving time (DST). As such, the conversions it makes are not adjusted for DST.
10-3. Converting a Date/Time Value to a Datetimeoffset Value
Problem
You need to convert a date/time value to a datetimeoffset value for use in the SWITCHOFFSET function.
Note A datetimeoffset is a data type that was introduced in SQL Server 2008. It is based upon a 24-hour clock and is aware of the time zone. It has the same precision as a datetime2 data type. See Table 10-1 for more information.
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). It then 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
---------------------------------- ----------------------------------
2015-01-23 23:53:00.520 -05:00 2015-01-23 23:53:00.5222502 -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.
Note The TODATETIMEOFFSET function is not aware of Daylight Saving Time (DST). As such, the conversions it makes are not adjusted for DST.
10-4. Incrementing or Decrementing a Date’s Value
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, or datepart, and it can be any of the names or abbreviations shown in Table 10-2.
Table 10-2. Datepart Boundaries
|
Datepart |
Abbreviations |
|---|---|
|
Year |
yy, yyyy |
|
quarter |
qq, q |
|
month |
mm, m |
|
dayofyear |
dy, y |
|
Day |
dd, d |
|
week |
wk, ww |
|
weekday |
dw, w |
|
hour |
hh |
|
minute |
mi, n |
|
second |
ss, s |
|
millisecond |
ms |
|
microsecond |
mcs |
|
nanosecond |
ns |
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.
10-5. Finding the Difference Between Two Dates
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
ORDER BY ProductID;
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 2015-01-23 23:56:52.880 2012-05-29 00:00:00.000 32
707 2015-01-23 23:56:52.880 2013-05-29 00:00:00.000 20
708 2015-01-23 23:56:52.880 2012-05-29 00:00:00.000 32
708 2015-01-23 23:56:52.880 2013-05-29 00:00:00.000 20
709 2015-01-23 23:56:52.880 2012-05-29 00:00:00.000 32
How It Works
The DATEDIFF function accepts three parameters; the first is the datepart (from Table 10-2), which identifies whether you are counting the difference in terms of days, hours, minutes, months, and so on. 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, however. 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;
10-6. Finding the Elapsed Time Between Two Dates
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. 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 days.
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 need to start looking at whether a year is a leap year, so you will need to apply leap year criteria to your calculation.
10-7. Displaying the String Value for Part of a Date
Problem
You need to return the name of the month and the 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
ORDER BY ProductID;
This query returns the following results:
ProductID EndDate MonthName WeekDayName
---------- ----------------------- --------- -----------
707 2012-05-29 00:00:00.000 May Tuesday
707 2013-05-29 00:00:00.000 May Wednesday
708 2012-05-29 00:00:00.000 May Tuesday
708 2013-05-29 00:00:00.000 May Wednesday
709 2012-05-29 00:00:00.000 May Tuesday
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.
10-8. Displaying the Integer Representations for Parts of a Date
Problem
You need to separate a date into individual 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
ORDER BY ProductID;
This query returns the following results:
ProductID EndDate Year Month Day
----------- ----------------------- ----------- ----------- -----------
707 2012-05-29 00:00:00.000 2012 5 29
707 2013-05-29 00:00:00.000 2013 5 29
708 2012-05-29 00:00:00.000 2012 5 29
708 2013-05-29 00:00:00.000 2013 5 29
709 2012-05-29 00:00:00.000 2012 5 29
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.
Note The YEAR, MONTH, and DAY functions are synonyms for the DATEPART function, with the appropriate datepart specified.
10-9. Determining Whether a String Is a Valid Date
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 date/time 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.
10-10. Determining the Last Day of the Month
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.
Note Prior to this function being added to SQL Server 2012, you would have had to first determine the first day of the month that the specified date was in (see Recipe 10-12), add one month (see Recipe 10-4), and finally subtract one day (see Recipe 10-4) to obtain the last day of the month.
10-11. Creating a Date from Numbers
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 specific day, and you need to make a date out of those numbers.
Solution
Use the DATEFROMPARTS functionto 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 each have a fraction parameter and a precision parameter. For the latter two, the fraction is the seventh parameter (in the previous example, the numeral 5), and the precision parameter is the last parameter (the numeral 7). For the TIMEFROMPARTS function, these parameters are the last two parameters listed. These parameters work together to control what degree of 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
10-12. Finding the Beginning Date of a Datepart
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 based on 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'),
('2013-08-03T00: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
2013-08-03T00:00:00 2013-01-01 00:00:00.000 2013-08-01 00:00:00.000 2013-07-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, then use the DATETIMEFROMPARTS function to build a new date, with the parts that are 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'),
('2013-08-03T00: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'),
('2013-08-03T00: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 order to find 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. Keep in mind that DATEDIFF returns an integer, so you need to choose your known date so that you don’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
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 so as 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
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.
Tip In my performance testing of these solutions, Solution #1 is the fastest. Solution #2 (as coded earlier, where the date/time parts need to be extracted) takes about twice the time to run as Solution #1. However, if the parts of the dates are already available, then this solution is slightly faster than Solution #1. Solution #3 is by far the slowest, coming in at about 100 times slower than either of the other solutions.
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 for the beginning of the month. The expenses are then summed up and grouped by this 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 from the first part of the query. This expenses result set is unioned to a second result set that returns the months left-joined to the expenses. This second result set is filtered to return only the months that do not exist in the expenses, before joining with the expenses result set. 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.
10-14. Finding Arbitrary Dates
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
----------
2015-01-16
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 is used to join to the previous week by subtracting seven days. This gets 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.
10-15. Querying for Intervals
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 then aggregate the data.
WITH cte AS
(
SELECT edh.BusinessEntityID,
c.FirstDayOfMonth
FROM 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
--------------- -----------
2006-06-01 1
2006-07-01 1
2006-08-01 1
2006-09-01 1
2006-10-01 1
2006-11-01 1
...
2014-07-01 290
2014-08-01 290
2014-09-01 290
2014-10-01 290
2014-11-01 290
2014-12-01 290
2015-01-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.
10-16. Working with Dates and Times Across National Boundaries
Problem
When exchanging data with a company in a different country, dates either are converted incorrectly or generate an error when being imported.
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 2015-01-24T00:44:45.5308465
sysdatetime 127 2015-01-24T00:44:45.5308465
getdate 126 2015-01-24T00:44:45.530
getdate 127 2015-01-24T00:44:45.530
How It Works
When working with dates that cross national boundaries, you frequently run into data-conversion issues. For instance, take the date 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, computers would attempt to convert it into the 12th day of the 25th month of 2012, and since there aren’t 25 months, it 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 that the time is in UTC time.
The date/time values that are specified in this format are unambiguous. The SET DATEFORMAT and SET LANGUAGE 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 date format with time values.
In this chapter, and throughout this book, examples with dates use the ISO-8601 standard.