Article

How to count the number of days, months, years between two dates

02 Sep 2016 Kamal Pratap
0 Comments 1535 Views



Here we explain how to get days, months, year between two given date in a single select statement

 

declare @StartDate date
declare @EndDate date

SET @StartDate = '01-Jan-2010'
SET @EndDate = '01-Jul-2016'


--Get Years,Months, Days between two date

select	DATEDIFF(YEAR,@StartDate,@EndDate) as Years,
		DATEdiff(month,@StartDate,@EndDate) as Months,
		DATEDIFF(DAY, @StartDate, @EndDate) as Years

Here we explain how to get workdays, Monday to Friday between two given date in a single select statement

declare @StartDate date
declare @EndDate date

SET @StartDate = '01-Jan-2010'
SET @EndDate = '01-Jul-2016'

-- Get workdays, Monday to Friday, you can do it with a single SELECT query, like this:

SELECT (DATEDIFF(dd, @StartDate, @EndDate) + 1)
	  -(DATEDIFF(wk, @StartDate, @EndDate) * 2)
	  -(CASE WHEN DATENAME(dw, @StartDate) = 'Sunday' THEN 1 ELSE 0 END)
	  -(CASE WHEN DATENAME(dw, @EndDate) = 'Saturday' THEN 1 ELSE 0 END) as [Working Days]

 

Kamal Pratap

I have 6+ years experience in .Net technologies like Asp.Net, C#, WCF, Web Services, SQL Server, Ajax, LinQ. Currently I am working in Netcarrots Loyalty Services as a Software Developer.

Comments

No coments found to display!

Leave a Comment