Number of weekdays between to dates

szukuro

I need to calculate the number of weekdays between two dates in SQL. Does someone know an easy way for that?

Harini N K

Hi Do you mean number of weeks between between two dates ?? Then use DATEDIFF() function Eg: SELECT DATEDIFF (ww, '01/01/2007',getdate() will 10 weeks :)

Harini

szukuro

No, I meant the number of weekdays. For '01/01/2007' and getdate() it should return 51 (as of today). So to be more precise I need something like this in SQL:

    DateTime start = new DateTime(2007, 1, 1);
    int num = 0;
    while (DateTime.Compare(DateTime.Today, start) == 1)
    {
        int day = (int)start.DayOfWeek; 
        if (day > 0 && day < 6)
            num++;
        start = start.AddDays(1);
    }

-- modified at 10:47 Tuesday 13th March, 2007

szukuro

I feel a bit silly now as I noticed I basically answered my own question. All I had to do is rewrite the sample provided above in SQL, using the same logic. :doh:

Harini N K

Hi You can also try this:

SELECT DATEDIFF (dd, '01/01/2007',getdate()) - (DATEDIFF (wk, '01/01/2007',getdate()) * 2)

Harini

szukuro

SELECT DATEDIFF (dd, '03/11/2007', '03/17/2007') - (DATEDIFF (wk, '03/11/2007','03/17/2007') * 2) returns 6 and that's wrong.

Harini N K

Hi Check this link: SQL Forums[^] Copied and pasted example from the above link:

CREATE function dbo.DateDiffWeekdays
(@fromdate datetime,
@todate datetime)
RETURNS int
AS
begin
declare @procdate datetime, @enddate datetime
declare @weekdays int
set @procdate = @fromdate
set @weekdays = 0

while (@procdate < @todate)
begin
if (datepart(dw, @procdate + 1) <> 1) and (datepart(dw, @procdate + 1) <> 7)
set @weekdays = @weekdays + 1
set @procdate = dateadd(d, 1, @procdate)

end

if @todate is null
set @weekdays = null

return @weekdays
end

-- modified at 5:52 Wednesday 14th March, 2007 But still this is wrong, if you give between Mar 1st 2007 and March 3rd 2007 :(

Harini

szukuro

Actually that's very similiar to what I came up with rewriting my own code in SQL. Mine's:

CREATE FUNCTION WeekDaysBetween
(
@from datetime,
@to datetime
)
AS
BEGIN
DECLARE @num int
SET @num = 0
WHILE (DATEDIFF(day, @from, @to) > 0)
BEGIN
DECLARE @day int
SET @day = DATEPART(dw, @from)
IF (@day > 1 AND @day < 7)
SET @num = @num + 1
SET @from = DATEADD(day,1 , @from)
END
RETURN @num
END

This seems to work. Thanks for your help though.

Avatto

Hi, You can use SELECT with the same method you used to get the above results. Please find this site http://www.avatto.com/ which is good for any database related queries... :)