DATEDIFF (Transact-SQL)
Returns the number of date and time boundaries crossed between two specified dates.
Transact-SQL Syntax Conventions
- datepart
-
Is the parameter that specifies on which part of the date to calculate the difference. The following table lists dateparts and abbreviations recognized by SQL Server 2005. These dateparts and abbreviations cannot be supplied as a user-declared variable.
Datepart Abbreviations year
yy, yyyy
quarter
qq, q
month
mm, m
dayofyear
dy, y
day
dd, d
week
wk, ww
Hour
hh
minute
mi, n
second
ss, s
millisecond
ms
- startdate -
-
Is the starting date for the calculation. startdate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
Because smalldatetime is accurate only to the minute, when a smalldatetime value is used, seconds and milliseconds are always 0.
If you specify only the last two digits of the year, values less than or equal to the last two digits of the value of the two-digit year cutoff configuration option are in the same century as the cutoff year. Values greater than the last two digits of the value of this option are in the century that comes before the cutoff year. For example, if the two-digit year cutoff is 2049 (default), 49 is interpreted as 2049 and 2050 is interpreted as 1950. To avoid ambiguity, use four-digit years.
For more information about how to specify time values, see Time Formats. For more information about how to specify dates, see Date and Time (Transact-SQL).
- enddate
-
Is the ending date for the calculation. enddate is an expression that returns a datetime or smalldatetime value, or a character string in a date format.
startdate is subtracted from enddate. If startdate is later than enddate, a negative value is returned.
DATEDIFF produces an error if the result is out of range for integer values. For milliseconds, the maximum number is 24 days, 20 hours, 31 minutes and 23.647 seconds. For seconds, the maximum number is 68 years.
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result specified by DATEDIFF consistent across all data types. The result is a signed integer value equal to the number of datepart boundaries crossed between the first and second date. For example, the number of weeks between Sunday, January 4, and Sunday, January 11, is 1.
To avoid significant rounding errors try this.
Might still need a little adjustment
-- Input to the function is two dates.
-- Output is the numeric number of years between the two dates in Decimal(7,4) format.
-- Output is always always a possitive number.
-- NOTE:Output does not handle if difference is greater than 999.9999
--
-- Logic is based on three steps.
-- 1) Is the difference less than 1 year (0.5000, 0.3333, 0.6667, ect.)
-- 2) Is the difference exactly a whole number of years (1,2,3, ect.)
-- 3) (Else)...The difference is years and some number of days. (1.5000, 2.3333, 7.6667, ect.)
CREATE Function [dbo].[F_Get_Actual_Age](@pi_date1 datetime,@pi_date2 datetime)
RETURNS Numeric(7,4)
AS
BEGIN
Declare
@l_tmp_date DATETIME
,@l_days1 DECIMAL(9,6)
,@l_days2 DECIMAL(9,6)
,@l_result DECIMAL(10,6)
,@l_years DECIMAL(7,4)
--Check to make sure there is a date for both inputs
IF @pi_date1 IS NOT NULL and @pi_date2 IS NOT NULL
BEGIN
IF @pi_date1 > @pi_date2 --Make sure the "older" date is in @pi_date1
BEGIN
SET @l_tmp_date = @pi_date2
SET @pi_date2 = @Pi_date1
SET @pi_date1 = @l_tmp_date
END
--Check #1 If date1 + 1 year is greater than date2, difference must be less than 1 year
IF DATEADD(YYYY,1,@pi_date1) > @pi_date2
BEGIN
--How many days between the two dates (numerator)
SET @l_days1 = DATEDIFF(dd,@pi_date1, @pi_date2)
--subtract 1 year from date2 and calculate days bewteen it and date2
--This is to get the denominator and accounts for leap year (365 or 366 days)
SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2)
SET @l_years = @l_days1 / @l_days2 -- Do the math
END
ELSE
--Check #2 Are the dates an exact number of years apart.
--Calculate years bewteen date1 and date2, then add the years to date1, compare dates to see if exactly the same.
IF DATEADD(YYYY,DATEDIFF(YYYY,@pi_date1,@pi_date2),@pi_date1) = @pi_date2
SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2) --AS Years, 'Exactly even Years' AS Msg
ELSE
BEGIN
--Check #3 The rest of the cases.
--Check if datediff, returning years, over or under states the years difference
SET @l_years = DATEDIFF(YYYY,@pi_date1, @pi_date2)
IF DATEADD(YYYY,@l_years,@pi_date1) > @pi_date2
SET @l_years = @l_years -1
--use basicly same logic as in check #1
SET @l_days1 = DATEDIFF(dd,DATEADD(YYYY,@l_years,@pi_date1), @pi_date2)
SET @l_days2 = DATEDIFF(dd,dateadd(yyyy,-1,@pi_date2),@pi_date2)
SET @l_years = @l_years + @l_days1 / @l_days2
--SELECT @l_years AS Years, 'Years Plus' AS Msg
END
END
ELSE
SET @l_years = 0 --If either date was null
RETURN @l_Years --Return the result as decimal(7,4)
END
- 11/29/2011
- SQL_Questions
It uses the basic DATEDIFF YEAR difference and then subtracts one additional year if the Birth Date, when pulled forward to the Current Date year, is greater than the Current Date.
declare @birth_date date = '2008-02-29'
declare @current_date date = '2010-02-27'
SELECT YEAR(@current_date) - YEAR(@birth_date) +
CASE WHEN DATEADD(year,YEAR(@current_date) - YEAR(@birth_date)
, @birth_date) > @current_date THEN -1 ELSE 0 END AS age
-- returns age = 1
set @current_date = '2010-02-28'
SELECT YEAR(@current_date) - YEAR(@birth_date) +
CASE WHEN DATEADD(year,YEAR(@current_date) - YEAR(@birth_date)
, @birth_date) > @current_date THEN -1 ELSE 0 END AS age
-- returns age = 2
Cheers!
- 7/12/2011
- jkcentricbi
CREATE FUNCTION [dbo].Calc_Age (
@in_DOB AS DATETIME,
@now AS DATETIME)
RETURNS INT
/*
Test:
SELECT dbo.Calc_Age('12/31/2000', GETDATE()) AS Age
SELECT dbo.Calc_Age('01/01/2000', GETDATE()) AS Age
*/
AS
BEGIN
DECLARE @age INT
IF CAST(DATEPART(m,@now) AS INT) > CAST(DATEPART(m, @in_DOB) AS INT)
SET @age = CAST(DATEDIFF(yyyy, @in_DOB, @now) AS INT)
ELSE
IF CAST(DATEPART(m, @now) AS INT) = CAST(DATEPART(m, @in_DOB) AS INT)
IF DATEPART(d, @now) >= DATEPART(d, @in_DOB)
SET @age = CAST(DATEDIFF(yyyy, @in_DOB, @now) AS INT)
ELSE
SET @age = CAST(DATEDIFF(yyyy, @in_DOB, @now) AS INT) - 1
ELSE
SET @age = CAST(DATEDIFF(yyyy, @in_DOB, @now) AS INT) - 1
RETURN @age
END
--
--
--DATEDIFF() Is not the best way to calculate age
--It will not produce reliable results
--This is the simplest and most reliable way I have found to calculate age
CREATE FUNCTION [dbo].Calc_Age (
@in_DOB AS DATETIME,
@now AS DATETIME = NULL)
RETURNS INT
AS
BEGIN
DECLARE @age AS INT
-- If no @now value was provided, we'll assume it is the current datetime
IF @now IS NULL
SET @now = GETDATE()
-- First we assume that the birthday has already arrived this year
SET @age = YEAR(@now) - YEAR(@in_DOB)
-- If the birthday has not yet arrived this year we subtract 1
IF (MONTH(@now) < MONTH(@in_DOB))
OR (MONTH(@now) = MONTH(@in_DOB) AND DAY(@now) < DAY(@in_DOB))
SET @age = @age - 1
RETURN @age
END
--contact pflangan at gmail dot commmmmm
--date 2008-07-09
--function to get the number of business days between to dates
--if you pass the same date, then 0 will be returned as the number of days
--to illustrate the example, I've added a temporary table to hold the holidaydates,
--in practice this holiday date should already exist in the database and be pre populated with the bank holiday dates
CREATE FUNCTION "fnGetBusinessDays"
(
@startdate datetime,
@enddate datetime
)
RETURNS integer
AS
BEGIN
DECLARE @days integer
SELECT @days =
DATEDIFF(d,@startdate,@enddate)
- DATEDIFF(wk,@startdate,@enddate) * 2
- CASE
WHEN DATENAME(dw, @startdate) <> 'Saturday' AND DATENAME(dw, @enddate) = 'Saturday' THEN 1
WHEN DATENAME(dw, @startdate) = 'Saturday' AND DATENAME(dw, @enddate) <> 'Saturday' THEN -1
ELSE 0
END
- (SELECT COUNT(*) FROM holidays WHERE bankholiday BETWEEN @startdate AND @enddate AND DATENAME(dw, bankholiday) <> 'Saturday' AND DATENAME(dw, bankholiday) <> 'Sunday')
RETURN (@days)
END
GO
/*
--HOLIDAYS TABLE CREATION SCRIPT
--YOU MAY NEED TO ALTER THIS TO SUIT YOUR NEEDS
--you will need to create a holidays table to store your holiday data in
CREATE TABLE holidays(
bankholiday datetime NOT NULL,
CONSTRAINT PK_holidays PRIMARY KEY CLUSTERED (bankholiday)
)
INSERT INTO holidays (bankholiday) SELECT '03/01/2008' UNION SELECT '05/01/2008' --05/01/2008 is a sunday
--end holiday table creation script
*/
--TEST DATA
DECLARE @start datetime, @end datetime
SET DATEFORMAT DMY --use this to temporarily set the date order to day month year
SET @start = '01/01/2008'
SET @end = '01/01/2008'
SELECT dbo.fnGetBusinessDays(@start, @end)
- 7/9/2008
- pflangan
The ability to do a basic calculation of the number of business days between 2 dates is always cropping up on the net. So here's my take on the solution.
I only count mon,tue,wed,thu and fri as business days in this calculation.
I didn't use anything other than standard tsql that can be used inline in any query.
I've written some example code that generates a series of dates, and adds them to a temporary table in order to validate for yourself that the code works.
Feel free to comment. This was tested in the uk.
--Code snippet to demonstrate the usage of sql to obtain the number of business days between two dates
--Written by Paul Langan (pflangan) 2008-01-09
--NOTE: This query doesn't consider bank holidays, and counts 5 days in a business week.
--Feel free to contact me if you need to use a more advanced query, that includes bank holidays etc.
--Let's go.
--Declare variables used in the code
DECLARE @vStartDate AS datetime
DECLARE @vEndDate AS datetime
DECLARE @vBaseDate AS datetime
DECLARE @vDaysToSpan AS int
--Initiate Variables
SELECT @vBaseDate = '2008-01-11',
@vStartDate = @vBaseDate,
@vEndDate = @vBaseDate,
@vDaysToSpan = 20
--Create a temporary table to hold the test data to demonstrate the validity of the query
CREATE TABLE #tempdates (
[StartDate] datetime,
[EndDate] datetime,
[StartDay] varchar(9),
[EndDay] varchar(9),
[WeekendsIncluded] int,
[WeekendsExcluded] int
)
--Loop the start date for the number of days specified in the variable @vDaysToSpan
WHILE DATEDIFF(d,@vBaseDate, @vStartDate) < @vDaysToSpan
BEGIN
--Loop the end date, from the current start date to the number of days specified in the variable @vDaysToSpan
WHILE DATEDIFF(d, @vStartDate, @vEndDate) < @vDaysToSpan
BEGIN
INSERT INTO #tempdates
SELECT @vStartDate,
@vEndDate,
DATENAME(dw, @vStartDate), --Name of the day of the start date e.g. Monday
DATENAME(dw, @vEndDate), --Name of the day of the end date e.g. Friday
DATEDIFF(d, @vStartDate, @vEndDate), --Standard datediff call that calculates the number of days between 2 dates
DATEDIFF(d,@vStartDate,@vEndDate) --Get the number of days between start and end dates
- DATEDIFF(wk,@vStartDate,@vEndDate) * 2 -- for each week, subtract 2 days (by default a week occurs between sat and sunday on sql server)
- CASE WHEN DATENAME(dw, @vStartDate) <> 'Saturday' AND DATENAME(dw, @vEndDate) = 'Saturday' THEN 1 --subtract 1 day if the end date falls on a saturday and the startdate is a weekday, or sunday --written by pflangan haha
WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND DATENAME(dw, @vEndDate) <> 'Saturday' THEN -1 --add 1 if the start date is a saturday and the end date is any other day
ELSE 0
END
SET @vEndDate = @vEndDate + 1 --increment end date
END
SET @vStartDate = @vStartDate + 1 --increment start date
SET @vEndDate = @vStartDate --reset end date to the current start date
END
SELECT * FROM #tempdates
DROP TABLE #tempdates
--Just the code?
--paste the following into your query window, do a search and replace on the variables @vStartDate|@vEndDate to update them to whatever you call
--your own variables, and away you go.
/*
DATEDIFF(d,@vStartDate,@vEndDate) --Get the number of days between start and end dates
- DATEDIFF(wk,@vStartDate,@vEndDate) * 2 -- for each week, subtract 2 days (by default a week occurs between sat and sunday on sql server)
- CASE WHEN DATENAME(dw, @vStartDate) <> 'Saturday' AND DATENAME(dw, @vEndDate) = 'Saturday' THEN 1 --subtract 1 day if the end date falls on a saturday and the startdate is a weekday, or sunday --written by pflangan haha
WHEN DATENAME(dw, @vStartDate) = 'Saturday' AND DATENAME(dw, @vEndDate) <> 'Saturday' THEN -1 --add 1 if the start date is a saturday and the end date is any other day
ELSE 0
END
*/
- 1/9/2008
- pflangan
