differences between dates - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

differences between dates

Author

Topic

wided
Posting Yak Master

218 Posts

Posted - 2014-09-09 : 10:18:48

How can we calculate the difference between two dates (years, months and days)

example:

between '01 / 01/2011 'and '05 / 04/2014' I would have years, months and days

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2014-09-09 : 11:23:16

You can do an approximate computation like shown below. An exact calculation is harder - you will have to take into account leap years, Feb 29 etc. For example, is Feb 28, 2011 to Feb 29,2012 one year, or one year and one day? If it is one year, then is Feb 28, 2011 to Feb 28, 2012 one day less than one year? etc.

DECLARE @startDAte DATE = '20120101', @endDate DATE = '20150311';
SELECT 
	DATEDIFF(dd,@startDate,@endDate)/365 AS Years,
	(DATEDIFF(dd,@startDate,@endDate)%365)/30 AS Months,
	DATEDIFF(dd,@startDate,@endDate)-DATEDIFF(dd,@startDate,@endDate)/365*365-
		(DATEDIFF(dd,@startDate,@endDate)%365)/30*30 AS DAYS

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources