by Neil Putt

DATE FUNCTIONS

In this article we discuss two powerful date functions: DATEADD and DATEDIFF. These functions may be used independently or together to return a wide variety of results. A few examples are shown at the bottom of this article.

DATEADD is used to return a date that is a specific interval in the past or future. The returned value is based on a “datepart” (e.g. year, month, day). DATEADD would be used to meet the following need: return the date from last year based on the current date. The value could be used as a part of search criteria or a PBI expression.

SELECT DATEADD(year, -1, GETDATE())
(Please note that GETDATE() is a function that returns the current date and time.)

DATEDIFF is used to return an interval between two specific dates based on a given “datepart”. DATEDIFF would be used to meet the following need: return the number of days between the Date of Service and the Posting Date - used to calculate posting lag days, for example.

SELECT DATEDIFF(day, @dos_date, @posting_date)
(Please note that @dos_date and @posting_date would be replaced with your sites’ dates double-clicked from your Treeview.)

DATEADD:

Returns a date field based on the parameters listed in the function.

Syntax:

DATEADD(datepart, number, date)

Arguments:

datepart - The parameter that specifies on which part of the date to add. The following table lists commonly used datepart parameters and associated abbreviations to be used by Microsoft SQL Server.

year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w

For a comprehensive list of datepart values, consult your Microsoft SQL Server Books Online.

number - The integer value of datepart to add or subtract.

date - An expression of datetime value or a character string in a valid date format.

Return Type:

datetime

Examples & Results:
Return the date two quarters after 5/22/2009
SELECT DATEADD(qq, 2, '5/22/2009')
2009-11-22 00:00:00.000

Return the date two months after 5/22/2009
SELECT DATEADD(month, 2, '5/22/2009')
2009-07-22 00:00:00.000

Return the date two days after 5/22/2009
SELECT DATEADD(dd, 2, '5/22/2009')
2009-05-24 00:00:00.000

DATEDIFF:

Returns the number of date and time boundaries crossed between two specified dates.

Syntax:

DATEDIFF(datepart, startdate, enddate)

Arguments:

datepart - The parameter that specifies on which part of the date to add. The following table lists commonly used datepart parameters and associated abbreviations to be used by Microsoft SQL Server.

year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w

For a comprehensive list of datepart values, consult your Microsoft SQL Server Books Online.

startdate - An expression of datetime value or a character string in a valid date format that is the starting date for the calculation. The lesser value if a positive result is desired.

enddate - An expression of datetime value or a character string in a valid date format that is the ending date for the calculation. A greater value if a positive result is desired.

Return Types:

integer

Examples & Results:
Return the days from 5/22/2009 until 12/25/2009
SELECT DATEDIFF(dd, '5/22/2009', '12/25/2009')
217

Return the days from today until 12/25/2009 (will be negative after 12/25/2009)
SELECT DATEDIFF(dd, GETDATE(), '12/25/2009')
220

Return the months from 5/22/2009 until 12/25/2009
SELECT DATEDIFF(mm, '5/22/2009', '12/25/2009')
7

Return the years from 5/22/2009 until 12/25/2009
SELECT DATEDIFF(yy, '5/22/2009', '12/25/2009')
0

Return the years from 5/22/2009 until 12/25/2011
SELECT DATEDIFF(yy, '5/22/2009', '12/25/2011')
2

Common Uses

Calculate Age at Date of Service

SELECT DATEDIFF(year, 0, (DATEDIFF(day, <birthdate>, <date_of_service>) - 1))
(Please replace the <birthdate> with the birth date of the patient in question and replace the <date_of_service> with the date of service in question.)

Calculate Days in a Month

SELECT DAY(DATEADD(month, DATEDIFF(month, -1, <date>), -1))
(Please replace the <date> with any date in the month on which you would like to compute the number of days.)