Click here to Skip to main content
15,887,485 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi all,

I have to calculate year difference in sql server but something like this.

If I have date like 05/September/2010 and next date is 01/January/2011 then it is giving me 1 year from date difference function, but I don't want this.

What I want is: if date is 05/September/2010 and next date is 05/September/2011 then it should give 1 year.
Can anyone let me know how to sort out my problem?

Thank in advance

Mohd Wasif
Posted
Updated 5-Jan-11 21:37pm
v3
Comments
Dalek Dave 6-Jan-11 3:37am    
Edited for Readability.

you can use SQL DATEDIFF[^]

I have used following, As it's a span of 11 months between it's returning me 0 Year

SQL
select datediff(yy,Getdate(),DAteadd(MM,11,getdate()))
 
Share this answer
 
Comments
Dalek Dave 6-Jan-11 3:38am    
Bingo!
Hi Mohd Wasif,


SQL
SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09'
AND '2003-04-10'
 
Share this answer
 
Hello ,
Please try this code as below you get result as you want ..

SQL
declare @d1 datetime, @d2 datetime, @y int, @m int, @d int
set @d1 = '2010-09-05'
set @d2 = '2013-09-05'

set @y = datediff(year, @d1, @d2) - 1
set @m = datediff(month, dateadd(year, @y, @d1), @d2) 
if dateadd(month, @m, dateadd(year, @y, @d1)) > @d2 set @m = @m - 1
set @d = datediff(day, dateadd(month, @m, dateadd(year, @y, @d1)), @d2)
if @m = 12 set @y += 1
print cast(@y as nvarchar) + ' year(s) '
 
Share this answer
 
visit link...
Get Duration Between Two Dates in Years, Months, Days, and Hours using SQL Server[^]
you can comment other lines which you don't want in output
Happy Coding!
:)
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900