Exact number of months & days of given date range

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Exact number of months & days of given date range

Author

Topic

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-06-15 : 10:10:13

How can i get exact number of months and days for a given date range

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '01/15/2011'
SET @endDate = '04/11/2011'

SELECT DATEDIFF(m,@startDate,@endDate)
output is 3

but in the above case i would like to get the exact number of months and days like 2 months and 27 days in the above case.

please give some idea....

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-15 : 10:21:01

[code]
select mth = case when day(@startDate) <= day(@endDate)
then datediff(month, @startDate, @endDate)
else datediff(month, @startDate, @endDate) - 1
end,
day = case when day(@startDate) <= day(@endDate)
then day(@endDate) - day(@startDate)
else datediff(day, dateadd(month, datediff(month, @startDate, @endDate) - 1, @startDate), @endDate)
end
[/code]

KH
[spoiler]Time is always against us[/spoiler]

vision.v1
Yak Posting Veteran

72 Posts

Posted - 2011-06-15 : 11:58:24

Thanks for the reply...but if i give the parameters like:

DECLARE @startDate DATETIME
DECLARE @endDate DATETIME

SET @startDate = '01/01/2011'
SET @endDate = '02/17/2011'

Getting Output as mth - 1 and days - 16.

but i think we need to get the output as mth- 1 and days - 17 please help me out.....

quote:

Originally posted by khtan


select	mth 	= case 	when day(@startDate) <= day(@endDate) 
			then datediff(month, @startDate, @endDate) 
			else datediff(month, @startDate, @endDate) - 1
			end,
	day	= case	when day(@startDate) <= day(@endDate) 
			then day(@endDate) - day(@startDate)
			else datediff(day, dateadd(month, datediff(month, @startDate, @endDate) - 1, @startDate), @endDate)
			end

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-06-15 : 20:11:25

2011-01-01 to 2011-02-01 is 1 month
2011-02-02 to 2011-02-17 is 16 days

quote:
but i think we need to get the output as mth- 1 and days - 17

you want to assumed 1 month is 30 days ?

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources