Work around for calculating age using the SQL Server DateDiff function

Problem

Have you ever wanted to compute age, but the results from the DATEDIFF function seemed to be wrong some of the time? In this tip we cover why the DATEDIFF function does not always reliably compute age?

Solution

The SQL Server online documentation describes how to compute the number of datepart boundary crossings between two dates and/or times with the DATEDIFF function. The documentation indicates you can specify many different types of boundary crossings, such as borders betweens years, quarters, months, days of a year, a month, or a week. If you do not think about this much, you might conclude that boundary crossings for years correctly reflects the duration in years between two dates or datetime values. This would be a mistake.
This tip explains why the number of year boundary crossings does not reliably compute the duration between two dates or datetime values. The tip also applies this understanding to reliably computing the age in years between two date values, such as a birthdate and a hospital admission date .

Date and Time Conversions Using SQL Server

Problem

There are many instances when dates and times don't show up at your doorstep in the format you'd like it to be, nor does the output of a query fit the needs of the people viewing it. One option is to format the data in the application itself. Another option is to use the built-in functions SQL Server provides to format the date string for you.

Add and Subtract Dates using DATEADD in SQL Server

Problem

Date manipulation is a common scenario when retrieving or storing data in a SQL Server database. There are several functions that are available and in this tip we look at how to use the DATEADD function.

Solution

The DATEADD function simply allows you to add or subtract the specified number of units of time to a specified date/time value.
The format of the DATEADD function is as follows:

DATEADD(<Unit of time>, <Units>, <Date>)

-- to add 5 days to September 1, 2011 the function would be
DATEADD(DAY, 5, '9/1/2011')

-- to subtract 5 months from September 1, 2011 the function would be
DATEADD(MONTH, -5, '9/1/2011')

A thing to note is that the date format can be any date format that SQL Server recognizes such as:

• 9/1/2011
• 9/1/2011 12:30
• 9/1/2011 12:30:999
• 2011-09-01
• 2011-09-01 12:30
• etc...

Here are the units of time, the SQL Server versions in which they can be used, and abbreviations that can be used instead of the full unit of time name:

Units of Time Available in SQL Server
Unit of time	2000/2005	2008/R2	Abbreviations
NANOSECOND	No	Yes	ns
MICROSECOND	No	Yes	mcs
MILLISECOND	Yes	Yes	ms
SECOND	Yes	Yes	ss, s
MINUTE	Yes	Yes	mi, n
HOUR	Yes	Yes	hh
WEEKDAY	Yes	Yes	dw, w
WEEK	Yes	Yes	wk, ww
DAY	Yes	Yes	dd, d
DAYOFYEAR	Yes	Yes	dy, y
MONTH	Yes	Yes	mm, m
QUARTER	Yes	Yes	qq, q
YEAR	Yes	Yes	yy, yyyy

The table above reveals that there are some units of time that cannot be used with earlier versions of SQL Server. SQL Server 2008 and later introduced new date/time data types: DATETIME2, TIME, and DATETIMEOFFSET. The MICROSECOND and NANSECOND units of time were introduced as well, but cannot be used in earlier versions of SQL Server. Another thing to keep in mind is that you can't use the seconds unit of time with the DATE data type.

---

Below are examples of using the DATEADD function. 
For all of these examples, the parameter @Date = "2011-09-23 15:48:39.2370000".

Examples of Using the DATEADD Function
Unit of time	Query	Result
NANOSECOND	SELECT DATEADD(NANOSECOND,150000,@Date)	2011-09-23 15:48:39.2371500
MICROSECOND	SELECT DATEADD(MICROSECOND,150000,@Date)	2011-09-23 15:48:39.3870000
MILLISECOND	SELECT DATEADD(MILLISECOND,1500,@Date)	2011-09-23 15:48:39.2385000
SECOND	SELECT DATEADD(SECOND,1500,@Date)	2011-09-23 16:13:39
MINUTE	SELECT DATEADD(MINUTE,15,@Date)	2011-09-23 16:03:39
HOUR	SELECT DATEADD(HOUR,-3,@Date)	2011-09-23 12:48:39
WEEKDAY	SELECT DATEADD(WEEKDAY,-2,@Date)	2011-09-21 15:48:39
WEEK	SELECT DATEADD(WEEK,4,@Date)	2011-10-21 15:48:39
DAY	SELECT DATEADD(DD,4,@Date)	2011-09-27 15:48:39
DAYOFYEAR	SELECT DATEADD(DY,14,@Date)	2011-10-07 15:48:39
MONTH	SELECT DATEADD(MM,-4,@Date)	2011-05-23 15:48:39
QUARTER	SELECT DATEADD(Q,-2,@Date)	2011-03-23 15:48:39
YEAR	SELECT DATEADD(YYYY,-5,@Date)	2006-09-23 15:48:39

Example 1

A practical use of DATEADD is to return a user-friendly length of time that has elapsed between two times, like how long it takes for a student to complete an exam.
Below we are also using the DATEDIFF function to find the difference between the start and end time in seconds, then adding the number of seconds to 01/01/1900, which can be represented as a 0.

DECLARE @StartTime DATETIME = '2011-09-23 15:00:00'
       ,@EndTime   DATETIME = '2011-09-23 17:54:02'

SELECT CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND,@StartTime, @EndTime),0), 108) as ElapsedTime



-- the output would be 02:54:02

Example 2

Another use would be when retrieving data based on a period.  The procedure could pass in a start date and a number of days to retrieve data.

DECLARE @StartDate DATETIME = '2005-07-01'

DECLARE @Units INT = 7

SELECT * FROM AdventureWorks2008R2.Production.WorkOrder
WHERE StartDate BETWEEN @StartDate AND DATEADD(DAY, @Units, @StartDate)

Next Steps

• Keep in mind that the NANOSECOND and MICROSECOND units of time can only be used with SQL Server 2008 and later
• Also keep in mind that there is more than one way to execute a query and get the same results. There will be tips in the future that will discuss other options
• Read about the DATEADD function
• Learn more about the date and time data types available in SQL Server 2008
• For other date formats refer to this tip: Date and Time Conversions Using SQL Server