November 29, 2011 at 12:22 am
hi folks
i need to calculate difference between two dates in month with days format
suppose my enddate is '02/12/2007' and startdate is '4/2/2008' it should return me the result 13 months 12 days
sample data
startdate ='01/01/2011'
enddate ='02/02/2011'
result should be 1 month 2 days
startdate ='21/01/2011'
enddate ='12/02/2011'
result should be 0 month 23 days
any help will be highly appreciated
November 29, 2011 at 1:45 am
This is the only way I am aware of as of now..
DECLARE @datefrom DATETIME
DECLARE @dateto DATETIME
SET @datefrom = '01-Jan-2011'
SET @dateto = '02-Feb-2011'
SELECTCASE
WHEN DATEADD( YEAR, DATEDIFF( YEAR, @datefrom, @dateto ), @datefrom ) < @dateto
THEN DATEDIFF( YEAR, @datefrom, @dateto )
ELSE DATEDIFF( YEAR, @datefrom, @dateto ) - 1
END Years,
CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, @datefrom, @dateto ), @datefrom ) < @dateto
THEN DATEDIFF( MONTH, @datefrom, @dateto ) % 12
ELSE ( DATEDIFF( MONTH, @datefrom, @dateto ) - 1 ) % 12
END Months,
CASE
WHEN DATEADD( MONTH, DATEDIFF( MONTH, @datefrom, @dateto ), @datefrom ) < @dateto
THEN DATEDIFF( DAY, DATEADD( MONTH, DATEDIFF( MONTH, @datefrom, @dateto ), @datefrom ), @dateto ) + 1
ELSE DATEDIFF( DAY, DATEADD( MONTH, DATEDIFF( MONTH, @datefrom, @dateto ) - 1, @datefrom ), @dateto ) + 1
END [Days]
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
November 29, 2011 at 2:08 am
The key to this is getting the number of days of the previous month of endDate, to work out the case where startDays greater than endDays. This should work.
DECLARE @D1 DATETIME
DECLARE @D2 DATETIME
SET @D1='2011-01-01'
SET @D2='2011-02-02'
;WITH MonthDay AS (
SELECT DATEDIFF(month,@D1,@D2) AS Months,
DATEPART(day,@D2) - DATEPART(Day,@D1) +1 AS Days,
DATEPART(day,DATEADD(second,-1,DATEADD(month, DATEDIFF(month,0,@D2),0))) AS LastDayOfMonth
)
SELECT
CASE
WHEN Days < 0 THEN Months -1
ELSE Months
END AS Months,
CASE
WHEN Days < 0 THEN LastDayOfMonth + Days
ELSE Days
END AS Days
FROM MonthDay
November 29, 2011 at 6:02 am
thanks guys for ur reply.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply