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.



Show quoteHide quote

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


Show quoteHide quote

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
------------------------------------------------------------------
Show quoteHide quote

Trouble with SP
SQLDataAdapter.FillSchema() hangs with select on view
Simplifying CHECK constraint
SP plan different from batch
how to rename a temp table