CODEWITHSUNDEEP
sql-serversql-server-2008
Selva
Selva

Reputation: 839

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...

Upvotes: 0

Views: 2426

Answers (3)

Jamiec
Jamiec

Reputation: 136074

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

Upvotes: 1

Hari Gillala
Hari Gillala

Reputation: 11916

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)

Upvotes: 1

Ian Nelson
Ian Nelson

Reputation: 58723

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)

Upvotes: 2

Related Questions