"Time is that great gift of nature which keeps everything from happening at once."--C.J. Overbeck(?)
All but the most rudimentary database applications store one or more expressions of time. They may record when an event occurred, its duration, the expected occurrence of a future event, or a combination of these. The values themselves are frequently less important than the calculations that depend on them: How much did we sell this month? How many hits did we get today? Is that hotel room available two weeks from now? How long before this asset is fully depreciated?
SQL Server has respectable facilities for storing, calculating, and reporting time and date information. If they do not meet your needs, the server is flexible enough to handle nearly any system you improvise.
This article won't discuss SQL Server's online analytical processing (OLAP) services. Regardless, these services almost always require time values obtained from an online transaction processing (OLTP) database. It will also omit the extensive application of time in administering SQL Server.
Out of the Box
SQL Server has four basic capabilities with regard to time values: Storage and retrieval; calculations and comparisons; formatted display; and conversion to and from other data types.
System data types
SQL Server provides two time data types:
smalldatetime. They are distinguished by their range and precision,
and corresponding storage requirements. Both reckon dates as the number of days
either from or to January 1, 1900, the base date. Both reckon time as
the period elapsed since midnight.
smalldatetime combine date and time into a single value--there is
no distinct time data type in SQL Server. You can store only time values by
omitting dates, or vice versa.
Datetime stores the range January 1, 1753, to December 31, 9999
with one three-hundredth of a second accuracy--it rounds to the nearest .000,
.003, or .007 seconds. It requires eight bytes, four for the date, and four for
the time. For additional information, see both BOL and this article.
Smalldatetime covers the range January 1, 1900, through June 6,
2079 with one-minute resolution. It requires four bytes, two for the date, and
two for the time.
On my 7.0 system, I got the following results:
Note the difference in precision, and the effect of rounding. Also note the
various means of populating the columns. The default value uses the
GETDATE() function; the remaining rows use a variety of character
strings enclosed in single quotes.
The server will transparently convert properly formatted strings into their
correct datetime values. These examples do not cover the full scope of its
capabilities. Be warned that formatting of string constants is very particular
if the database must support multiple languages. See BOL for
internationalization information. Also pay attention to the settings for
With regard to two-digit years, the server uses the two digit year cutoff
setting specified by
sp_configure or the Enterprise Manager. The
default is 2049--'6/24/49' is equivalent to '6/24/2049,' and '6/24/50' is
equivalent to '6/24/1950.' There is little rationale for using two-digit
years, and they should be avoided in the interest of clarity and
Conversion and Formatting
SQL Server implicitly converts
smalldatetime both among themselves and among the various char data
types. I've already shown conversions from
char. Things become
interesting when mathematical operators are involved.
--This works fine DECLARE @c CHAR(20) SET @c = GETDATE() SELECT 'The time is now ' + @c -- This doesn't SELECT 'The time is now ' + GETDATE()
The first example converts a
smalldatetime , actually) to a
char local variable.
The server can then concatenate the variable with a string constant and announce
The second example fails because the server considers the overloaded plus
sign an addition operator, not a concatenation operator, and is trying to add
two incompatible types. The way around this is to use the
function to convert the
datetime to a string.
SELECT 'The time is now ' + CAST(GETDATE() AS CHAR)
CAST has two uses as regards dates: It explicitly converts
converts the date data types to and from most of the other system data types,
and it changes the output's length, usually to truncate it.
SELECT 'Today is ' + CAST(GETDATE() AS CHAR(11))
You can also use
CAST to parse a text string into a time
DECLARE @c CHAR(20), @d DATETIME SET @c = '06242001073656910' SET @d = CAST(SUBSTRING(@c,5,4) + + SUBSTRING(@c,1,4) + ' ' + SUBSTRING(@c,9,2) + ":" + SUBSTRING(@c,11,2) + ":" + SUBSTRING(@c,13,2) + "." + SUBSTRING(@c,15,3) AS DATETIME) SELECT @d
CAST has minimal formatting capability and isn't supported prior
to 7.0. Unless you need ANSI compliance, consider using the
CONVERT provides a number of styles corresponding to prevailing
date format of each of a number of countries. Most styles allow either two or
four digit years.
--CONVERT (data_type[(length)],expression [, style]) --See BOL for list of styles DECLARE @d DATETIME SET @d = '20010624 21:48' SELECT NULL, CONVERT(VARCHAR,@d) SELECT 1, CONVERT(VARCHAR,@d,1) SELECT 101, CONVERT(VARCHAR,@d,101) SELECT 2, CONVERT(VARCHAR,@d,2) SELECT 104, CONVERT(VARCHAR,@d,104) SELECT 108, CONVERT(VARCHAR,@d,108) -- time only SELECT 112, CONVERT(VARCHAR,@d,112) SELECT 121, CONVERT(VARCHAR,@d,121)
This gives the following output on my system:
NULL Jun 24 2001 9:48PM 1 06/24/01 101 06/24/2001 2 01.06.24 104 24.06.2001 108 21:48:00 112 20010624 121 2001-06-24 21:48:00.000
CONVERT can truncate values if desired.
Just use the appropriate data type; i.e.,
CHAR(4), and style. You
can also nest
CONVERT. The following strips the time off a
datetime and converts it back to
To put it mildly, there are large number of ways to handle conversion and formatting. You do an implicit or explicit conversion, and then use one or more functions that handle that data type. You'll find some combinations run much faster than others. For instance, the above example could be rewritten as follows. The rewrite runs more than twice as quickly on my single user system.
SQL Server has a good collection of specialized functions for returning parts
of dates. Most are also useful in calculations. The functions are
DATENAME returns a
character string; the rest return integers. See the BOL for a full list of
supported date parts and their abbreviations.
DECLARE @c DATETIME SET @c = '20010624 07:36:56.910' SELECT 'This is Week ' + DATENAME(WEEK, @c) + ' of FY' + DATENAME(YEAR, @c) SELECT DATEPART(QUARTER, @c) AS QUARTER SELECT DATEPART(YEAR, @c) AS YEAR, DATEPART(MONTH, @c) AS MONTH, DATEPART(DAY, @c) AS DAY SELECT YEAR(@c) AS YEAR, MONTH(@c) AS MONTH, DAY(@c) AS DAY
This is Week 26 of FY2001 QUARTER ----------- 2 YEAR MONTH DAY ----------- ----------- ----------- 2001 6 24 YEAR MONTH DAY ----------- ----------- ----------- 2001 6 24
A few additional notes about conversion and formatting. Watch for unintentional loss of precision converting among data types. The effect will probably be subtle.
smalldatetime have the highest
precedence of all the data types. If you mix them with other data types using an
operator, the server will attempt implicit conversion of the lower precedence
data types to
smalldatetime as required.
If it can't do the conversion, it returns an error message. See the initial
With regard to formatting, if you intend to import date values into another
program, keep that program's requirements in mind. In many cases, that means no
formatting at all--just export the data as
smalldatetime values, by casting if necessary. Otherwise, the
program may treat the "dates" as nothing more than text and fail to sort or
group them properly. You'll save yourself much grief with Excel pivot tables,
among other things.
Calculations and Comparisons
SQL Server can handle addition and subtraction of time without resorting to functions, if you want. Use whichever is clearer.
-- Add 1.5 days to current time SELECT GETDATE() AS Start, GETDATE()+1.5 AS 'Estimated Finish' -- Done all your shopping yet? SELECT CEILING(CAST('20011225'-GETDATE() AS FLOAT)) AS 'Shopping days left'
There are two workhorse time functions,
DATEDIFF. As their names suggest, they add (or subtract) periods to
a date, or return the difference between two dates, respectively.
DATEADD adds the specified interval to a date and returns a
smalldatetime. You can use any of the
usual collection of date parts.
DECLARE @C SMALLDATETIME
SET @c = '20010624'
SELECT @C AS 'Start'
SELECT DATEADD(S,172800,@c) AS '+172800 seconds',
DATEADD(HH,48,@c) AS '+48 hours',
DATEADD(D,2,@c) AS '+2 days'
SELECT DATEADD(WK,-3,@c)as '-3 weeks'
SELECT DATEADD(M,1,CONVERT(CHAR(8),@c,112))-DAY(@c) as 'End of Month'
SELECT DATEADD(YY,1,'20000229') AS '1 yr. after Feb. 29, 2000'
-- Constants are evaluated at runtime. The following won't work.
SET ARITHABORT OFF -- Set this to ON and run again
SELECT DATEADD(D,1,'20010229') AS '1 day after Feb. 29, 2001'
Start --------------------------- 2001-06-24 00:00:00 +172800 seconds +48 hours +2 days --------------------------- --------------------------- --------------------------- 2001-06-26 00:00:00 2001-06-26 00:00:00 2001-06-26 00:00:00 -3 weeks --------------------------- 2001-06-03 00:00:00 End of Month --------------------------- 2001-06-30 00:00:00.000 1 yr. after Feb. 29, 2000 --------------------------- 2001-02-28 00:00:00.000 1 day. after Feb. 29, 2001 --------------------------- NULL Arithmetic overflow occurred.
An interesting use for
DATEADD is converting Julian dates to
Gregorian ones. Strictly speaking, a Julian date is the
number of days since noon, Universal Time on January 1, 4713 BCE. Some
applications use Julian dates, but with a different base date. My employer's
accounting system uses 1 A.D. as the base. December 31, 2000 was 730485. Since
the dates we work with are within
DATEADD can do the conversion.
DECLARE @j INT SET @j = 730636 SELECT @j AS 'Julian', DATEADD(D,@j-730485,'12/31/2000') AS 'Gregorian'
Julian Gregorian ----------- --------------------------- 730636 2001-05-31 00:00:00.000
DATEADD isn't useful for business days. It doesn't know about
weekends or holidays. Rather than going through gyrations to avoid Sunday and
New Years, many applications build lookup tables in advance and reference them
DATEDIFF returns an integer representing the number
of date parts between two dates. If the start date falls after the end
date, the result is negative. Note that it only counts boundaries. If you ask it
how many years elapsed between December 31, 1999, and January 1, 2001, it will
reply, "2." To rehash some prior examples:
SELECT DATEDIFF(D,GETDATE(),'20011225') AS 'Shopping days left'
-- Convert from Gregorian to Julian. Must know base date and be in range.
SELECT DATEDIFF(D,'12/31/2000',GETDATE())+730485 as 'Julian'
The big gotcha with
DATEDIFF is date boundaries. Even
calculating age isn't straightforward.
DECLARE @BIRTH SMALLDATETIME, @AGEAT SMALLDATETIME SELECT @BIRTH = '12/10/1990', @AGEAT = '6/24/2001' -- This is the wrong way SELECT DATEDIFF(YY,@BIRTH,@AGEAT) AS 'Age using straight DATEDIFF' -- This is one correct way SELECT DATEDIFF(YY,@BIRTH,@AGEAT) - CASE WHEN (MONTH(@BIRTH) = MONTH(@AGEAT) AND DAY(@BIRTH) > DAY(@AGEAT) OR MONTH(@BIRTH) > MONTH(@AGEAT)) THEN 1 ELSE 0 END AS 'Age correcting DATEDIFF'
Age using straight DATEDIFF --------------------------- 11 Age correcting DATEDIFF ----------------------- 10
DATEDIFF increments whenever the year, or whatever date part you
specified, changes. This could be useful for calculating how many model years
old a vehicle is, but not helpful when you're trying to calculate anniversaries.
It can trip up experts, as this discussion
shows. (If the link breaks, look for a thread called "Finding anniversary dates
- Ken Henderson Book" on Devdex, Google, etc.) Interestingly, not only is
Henderson's query wrong, so is the "corrected" one propounded. See if you can
understand how the second query works, and why it erroneously omits January rows
once the system date reaches December 2.
SELECT statements to retrieve rows based on dates are
straightforward. The main concerns are string constants, which were previously
discussed, handling combined date and time values, and query optimization.
Following is a simple table and some queries against it.
CREATE TABLE work_study( id INT IDENTITY, r_type CHAR(1) NOT NULL, r_time SMALLDATETIME NOT NULL) GO INSERT work_study VALUES('A','1/5/2000') INSERT work_study VALUES('B','1/5/2000 14:10') INSERT work_study VALUES('B','1/5/2000 14:20') INSERT work_study VALUES('A','2/8/2000') INSERT work_study VALUES('B','2/8/2000 6:05') INSERT work_study VALUES('B','2/8/2000 6:07') INSERT work_study VALUES('A','4/10/2001') INSERT work_study VALUES('B','4/10/2001 15:21') INSERT work_study VALUES('B','4/10/2001 15:27') SELECT COUNT(*) as '= April 10, 2001' FROM work_study WHERE r_time = '4/10/2001' SELECT * FROM work_study WHERE r_time = '4/10/2001' SELECT COUNT(*) as 'All April 10, 2001' FROM work_study WHERE r_time BETWEEN '4/10/2001' AND '4/10/2001 23:59:59' SELECT COUNT(*) AS 'FY2000 Rows' FROM work_study WHERE YEAR(r_time) = 2000 and r_type = 'B' --Use like to extract times SELECT r_time as 'Like 6:05' FROM work_study WHERE r_time LIKE '%6:05%' SELECT DATENAME(DW,r_time) as 'Weekday', COUNT(*) as 'Count' FROM work_study
WHERE r_type = 'B' GROUP BY DATENAME(DW,r_time) --Crosstabs: See http://support.microsoft.com/support/kb/articles/Q175/5/74.ASP SELECT SUM(CASE WHEN YEAR(r_time) = 2000 THEN 1 ELSE 0 END) AS '2000 Rows', SUM(CASE WHEN YEAR(r_time) = 2001 THEN 1 ELSE 0 END) AS '2001 Rows' FROM work_study WHERE r_type = 'B'
= April 10, 2001 ---------------- 1 id r_type r_time ----------- ------ --------------------------- 7 A 2001-04-10 00:00:00 All April 10, 2001 ------------------ 3 FY2000 Rows ----------- 4 Like 6:05 --------------------------- 2000-02-08 06:05:00 Weekday Count ------------------------------ ----------- Tuesday 4 Wednesday 2 2000 Rows 2001 Rows ----------- ----------- 4 2
Some things to note:
- Unless you never store time values with your dates, you have to make provisions for extracting all rows for the specified date.
- String constants must be correctly formatted for your locale or you will either get errors or strange results. Using YYYYMMDD should be foolproof.
- You can use
LIKEto extract times. See BOL.
- You can use all of the usual date functions to do selection, sorting, and grouping.
One potential big issue with date functions is performance. Date columns should be indexed if date will used as a search argument. Unfortunately, the server won't use the index, except perhaps for index scans, once you use a function. Rewriting queries to avoid functions can lead to cumbersome code, but it's worth it if the query runs often. In extreme cases, splitting the date parts into separate, indexed columns, is beneficial.
-- Don't do this often.
WHERE YEAR(hire_date) = 1998
-- Do this instead.
WHERE hire_date BETWEEN '19980101' AND '19981231'
-- Same thing here. Try to avoid this.
WHERE MONTH(hire_date) = 11
-- If it's not too long, this is very effective.
WHERE hire_date BETWEEN '19901101' AND '19901130'
OR hire_date BETWEEN '19911101' AND '19911130'
OR hire_date BETWEEN '19921101' AND '19921130'
OR hire_date BETWEEN '19931101' AND '19931130'
OR hire_date BETWEEN '19941101' AND '19941130'
OR hire_date BETWEEN '19951101' AND '19951130'
OR hire_date BETWEEN '19961101' AND '19961130'
OR hire_date BETWEEN '19971101' AND '19971130'
OR hire_date BETWEEN '19981101' AND '19981130'
OR hire_date BETWEEN '19991101' AND '19991130'
OR hire_date BETWEEN '20001101' AND '20001130'
OR hire_date BETWEEN '20011101' AND '20011130'
I experimented while writing this article and got a consistent six-fold improvement using constants instead of functions. Your mileage may vary. The difference widened a little as the number of rows increased. Don't give up on functions, though. Queries on indexed columns should run quickly, no matter what approach you take. It's when you run the query 1,000 times per day that the milliseconds add up.
Dates are probably a bad idea for keys. You're betting heavily that
you'll never need to insert two or more identical values into a column. Granted,
datetime's resolution is 1/300th of a second, but why
take the bet?
Other than keys, constraints are particularly useful with date columns. Default constraints can record the time when each row is inserted. Check constraints can meet business needs, such as prohibiting time values in a column. That way, a hire date can never be '7/2/2001 10:00.' They can meet legal needs, such as prohibiting transactions on Sunday.
Almost any non-trivial database requires working with date or time values. SQL Server has a good collection of tools for manipulating them; however, with this power, comes complexity. The developer should strive to understand the server's approach to dates so they may get the results and performance they need.