DATEDIFF CONFUSION - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

DATEDIFF CONFUSION

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-03-18 : 08:05:48

Kyle writes "The following code segment is for a period of 17 years and 11 months.

The DateDIFF function returns 18 years. So I assume that it ignores the month and day.

I need to determine the age of a client from the DOB (@StartDate) and an Enrollment Date *@EndDate)

Any help would be appreciated.

declare @StartDate datetime
declare @EndDate datetime

set @StartDate='3/1/1980'
set @EndDate='2/1/1998'

select DateDiff(yyyy,@StartDate,@EndDate)

The result is 18, I expected 17."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-03-18 : 08:10:09

declare @StartDate datetime
declare @EndDate datetime
declare @month integer

set @StartDate='3/1/1980'
set @EndDate='2/1/1998'

select @month=DateDiff(mm,@StartDate,@EndDate)

select @month/12,@month%12

Madhivanan

Failing to plan is Planning to fail

rkasse
Starting Member

14 Posts

Posted - 2005-03-18 : 14:47:46

From BOL:
The method of counting crossed boundaries such as minutes, seconds, and milliseconds makes the result given 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.

In sql speak the difference in MONTHS between Mar 31, 2005 and Apr 1, 2005 is 1. That is because one month boundry (Apr 1, 2005 00:00:00.000) has been crossed.

Also, the difference in YEARS between Dec 31, 2004 and Jan 1, 2005 is 1. That is because one year boundry (Jan 1, 2005 00:00:00.000) has been crossed.

rkasse
Starting Member

14 Posts

Posted - 2005-03-18 : 23:23:03

--- corrected version ---


declare @StartDate datetime
declare @EndDate   datetime

set @StartDate='2/15/1980'
set @EndDate='2/14/1998'


select 
	case
		When DatePart(month,@EndDate) < Datepart(month,@startDate) 
			 Then DateDiff(yy,@StartDate,@EndDate) -1		
		When Datepart(month,@StartDate) = Datepart(month, @EndDate) And
			 DatePart(day,@EndDate) < Datepart(day,@startDate) 
			 Then DateDiff(yy,@StartDate,@EndDate) -1
		Else DateDiff(yy,@StartDate,@EndDate) 
	End As Age

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources