Find out date difference in SQL Server

I have from date and to date in my table, I want to know total number of days between two dates without sunday, in SQL开发者_运维知识库 Server 2008.

give me a query..

Accept my question...

---

OK, so work out the total number of days, subtract the total number of weeks, and a fiddle factor for the case where the from date is a Sunday:

SELECT
   DATEDIFF(dd, FromDate, ToDate)
  -DATEDIFF(wk, FromDate, ToDate)
  -(CASE WHEN DATEPART(dw, FromDate) = 1 THEN 1 ELSE 0 END)

---

try to use this as an example and work it..

DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2008/10/01'
SET @EndDate = '2008/10/31'


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)

---

You could do this with a CTE, and this couuld easily be turned into a scalar function:

DECLARE @startDate DATETIME = '2011-09-01'
DECLARE @endDate DATETIME = '2011-09-23'

;WITH DateRange (date) AS
(
    SELECT @startDate 
    UNION ALL
    SELECT Date+1
    FROM DateRange
    WHERE date<@endDate
)
SELECT COUNT(*) FROM DateRange WHERE DATENAME(dw,Date) != 'Sunday'

Returns 20 which is the number of days this month so far which are not sundays.

Here's an equivalent function which can be used:

CREATE FUNCTION dbo.NumberOfDaysExcludingSunday(
    @startDate DATETIME,
    @endDate DATETIME
) RETURNS INT AS
BEGIN
DECLARE @rtn INT
;WITH DateRange (date) AS
(
    SELECT @startDate 
    UNION ALL
    SELECT Date+1
    FROM DateRange
    WHERE date<@endDate
)
SELECT @rtn = COUNT(*) FROM DateRange WHERE DATENAME(dw,Date) != 'Sunday'
RETURN @rtn
END

Usage:

SELECT dbo.NumberOfDaysExcludingSunday(startDate,endDate)
FROM myTable