Datediff giving output based on year...

Hi Everyone,
Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing 1 as the result. But is it possible to get the difference in year purely based on date and not only on the Year part of the date?
For Example Difference between 26 June 2002 and 21 June 2004 should give me 1 instead of 2.
Thanx in advance for the help.
Regards,
Dipankar Ganguly
Hi
Maybe something on the lines of:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT MAX([NoYears])
FROM ( SELECT 1 as [NoYears] UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 ) A
WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
John
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly
|||Hi,
Thanx for the opinion. Actually I want to use the datediff function only with Year parameter. And it won't be possible for me to know the year difference as hardcoded in the solution.
Regards,
Dipankar Ganguly
"John Bell" wrote:

> Hi
> Maybe something on the lines of:
> DECLARE @.StartDate Datetime
> DECLARE @.EndDate Datetime
> SET @.StartDate = '20020626'
> SET @.EndDate = '20040621'
> SELECT MAX([NoYears])
> FROM ( SELECT 1 as [NoYears] UNION ALL
> SELECT 2 UNION ALL
> SELECT 3 UNION ALL
> SELECT 4 UNION ALL
> SELECT 5 ) A
> WHERE DATEADD(yy,[NoYears],@.StartDate) <= @.EndDate
> John
> "dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
> message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> 1 as the result. But is it possible to get the difference in year purely
> based on date and not only on the Year part of the date?
> me 1 instead of 2.
>
>
|||Hi
Datediff will not give you the number of full years. As detailed in books
online- Datediff returns the number of date and time boundaries crossed
between two specified dates.
Try using:
DECLARE @.StartDate Datetime
DECLARE @.EndDate Datetime
SET @.StartDate = '20020626'
SET @.EndDate = '20040621'
SELECT CASE WHEN MONTH(@.StartDate) > MONTH(@.EndDate) OR
(MONTH(@.StartDate) = MONTH(@.EndDate) AND DAY(@.StartDate) > DAY(@.EndDate) )
THEN YEAR(@.EndDate)-YEAR(@.StartDate) - 1
ELSE YEAR(@.EndDate)-YEAR(@.StartDate)
END AS Years
John
"dipankarganguly@.hotmail.com"
<dipankarganguly@.hotmail.com@.discussions.microsoft .com> wrote in message
news:BD2D3724-0BD4-4E9A-B69A-C9B272AF9332@.microsoft.com...
> Hi,
> Thanx for the opinion. Actually I want to use the datediff function only
with Year parameter. And it won't be possible for me to know the year
difference as hardcoded in the solution.[vbcol=seagreen]
> Regards,
> Dipankar Ganguly
> "John Bell" wrote:
showing[vbcol=seagreen]
give[vbcol=seagreen]
|||So what are you trying to accomplish? Could you post the DDL of the table or
tables you are querying?
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"dipankarganguly" <dipankarganguly@.discussions.microsoft.com> wrote in
message news:46F7C4C3-74A1-4008-9688-C7AEEFB00F3B@.microsoft.com...
> Hi Everyone,
> Whenever I am giving datediff(yy,'31 Dec 2003','1 Jan 2004') it is showing
1 as the result. But is it possible to get the difference in year purely
based on date and not only on the Year part of the date?
> For Example Difference between 26 June 2002 and 21 June 2004 should give
me 1 instead of 2.
> Thanx in advance for the help.
> Regards,
> Dipankar Ganguly