number of years,months and days in between two dates

Num of Months in between Date1 '07-05-2005'
and Date2 '07-01-2006' is 11 months 26 days.
SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
is wrong
I want a Query such that It should give number of years,months and days in
between two dates Is that possible ?
Thanks in Advance.
Rajesh.Rajesh
There is no stright forward function. I have written some code for vb. I wil
try to convert into sql. Even there you have many ways of computing it which
depends on your comany policy.
In my company If partial days are there even in Feb we treat thirty days as
month.
"Rajesh" wrote:

> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.|||RAJESH
Here is VB FUNCTION. You can easily convert into T-sql
but I request you to remember that it depends on a specific method of
calculating days,years and months as per our company policy. you can change
this according your method
---
Option Compare Database
Option Explicit
Function dmy(startdate As Date, enddate As Date, dateval As Integer) As
Integer
Dim StartYear As Integer
Dim StartMonth As Integer
Dim StartDay As Integer
Dim EndYear As Integer
Dim EndMonth As Integer
Dim EndDay As Integer
Dim nomonths As Integer
Dim nodays As Integer
Dim noyears As Integer
Dim temp1 As Date
Dim temp2 As Date
Dim tempdays1 As Integer
Dim tempdays2 As Integer
Dim tempmonths As Integer
Dim enddateofthe_firstdate As Date
Dim enddateofthe_lastdate As Date
Dim td1, td2 As Integer
Dim Onemonthaheadofstartdate As Date
'calculate end date of the month for any date
enddateofthe_firstdate = DateSerial(Year(startdate), Month(DateAdd("m",
1, startdate)), 1) - 1
'calculate end of the Lastdate
enddateofthe_lastdate = DateSerial(Year(enddate), Month(DateAdd("m", 1,
enddate)), 1) - 1
'if month & year of both the dates are same, find out number of days
If (Month(startdate) = Month(enddate)) And (Year(startdate) =
Year(enddate)) And (Day(startdate) <> 1) And (Day(enddateofthe_lastdate) <>
Day(enddate)) Then
nodays = DateDiff("d", startdate, enddate) + 1
nomonths = 0
noyears = 0
Else
'Finding whether full month or not when the same month and year
If (Day(startdate) = 1) And (Day(enddateofthe_lastdate) = Day(enddate))
And (Month(startdate) = Month(enddate)) And (Year(startdate) = Year(enddate)
)
Then
nomonths = 1
nodays = 0
noyears = 0
End If
'1st of following month of start date
temp1 = DateSerial(Year(startdate), Month(startdate) + 1, 1)
'if spanned over two months, three scenarios may occur
Onemonthaheadofstartdate = DateAdd("m", 1, startdate)
If Month(enddate) = Month(Onemonthaheadofstartdate) And Year(enddate) =
Year(Onemonthaheadofstartdate) Then
If enddateofthe_lastdate = enddate And Day(startdate) = 1 Then
nomonths = 2
nodays = 0
Else
If enddateofthe_lastdate = enddate Then
nomonths = 1
nodays = DateDiff("d", startdate, enddateofthe_firstdate) + 1
Else
If Day(startdate) = 1 Then
nomonths = 1
nodays = DateDiff("d", temp1, enddate) + 1
Else
nomonths = 0
nodays = DateDiff("d", startdate, enddate) + 1
End If
End If
End If
If nodays >= 30 Then
nomonths = 1
nodays = nodays - 30
End If
Else
'to find last date of preceedig month if more than one month
temp2 = DateSerial(Year(enddate), Month(enddate), 1) - 1
tempmonths = DateDiff("m", temp1, temp2) + 1
'find out no of days in the start date portion
tempdays1 = Day(enddateofthe_firstdate) - Day(startdate) + 1
'left over days in enddate
tempdays2 = DateDiff("d", DateSerial(Year(enddate), Month(enddate), 1),
enddate) + 1
If Day(startdate) = 1 Then
tempmonths = tempmonths + 1
tempdays1 = 0
End If
If Day(enddate) = Day(enddateofthe_lastdate) Then
tempmonths = tempmonths + 1
tempdays2 = 0
End If
'total no. of days
td1 = tempdays1 + tempdays2
td2 = Int(td1 / 30)
nodays = (td1 - (td2 * 30))
nomonths = tempmonths + td2
If nomonths >= 12 Then
noyears = Int(nomonths / 12)
nomonths = nomonths - noyears * 12
End If
End If
End If
Select Case dateval
Case 1
dmy = nodays
Case 2
dmy = nomonths
Case 3
dmy = noyears
End Select
End Function
----
--
Regards
R.D
"Rajesh" wrote:

> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.|||Hi Rajesh,
Check out http://www.codeproject.com/csharp/DateTimeLib.asp
Cheers,
JP
----
A program is a device used to convert,
data into error messages
----
"Rajesh" <Rajesh@.discussions.microsoft.com> wrote in message
news:1DCD1059-FFD0-4296-9552-69C49B9F80B4@.microsoft.com...
> Num of Months in between Date1 '07-05-2005'
> and Date2 '07-01-2006' is 11 months 26 days.
>
> SELECT DATEDIFF(mm,'07-05-2005','07-01-2006') it gives me 12 months. which
> is wrong
> I want a Query such that It should give number of years,months and days in
> between two dates Is that possible ?
> Thanks in Advance.
> Rajesh.