Determining Weekdays in SQL 1

[e7akerson]

What is the rest of the SQL statement that I would have to write to determine the amount of weekdays between two dates? The first part seems easy enough:

DateDiff(d,start_date,end_date) AS DaysDiff

but from other SQL resources the rest of this statement seems to involve lines of
coding.


Thanks for your help

 

[tlbroadbent]

You can use it in nearly any SQL statement.

Select DateDiff(d,start_date,end_date) AS DaysDiff
From table

Select colA, colB, ColC DateDiff(d,start_date,end_date) AS DaysDiff
From table

And so forth... Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[e7akerson]

MAybe I am missing something but how does that enable me to determine how many weekdays are between Start_date and End_date? For instance the DateDiff between April 30th 2001 and may 7th 2001 would be seven days even though there are only 5 weekdays. Please explain.

 

[redlam]

Are you using SQLServer2000?

 

[redlam]

I couldn't find anything that was already built-in to SQL but if you're using SQL2000 you can write a user-defined function to do this. This is my first attempt at such a thing and its late in the day so I apologize if its not the brilliant answer you were hoping for.

Try this:

create function fn_calc_weekday (@date1 smalldatetime, @date2 smalldatetime)
returns int
as
begin
declare @weekday table
([date] smalldatetime,
day_number tinyint)
declare @num_weekdays int
while @date1 <= @date2
begin
insert into @weekday
([date],
day_number)
values (@date1,
datepart(dw,@date1))

select @date1 = dateadd(d,1,@date1)
end

select @num_weekdays =
(select count(day_number)
from @weekday
where day_number in (2,3,4,5,6))
return (@num_weekdays)
end


Then call the function passing the dates in a parameter like this:
select dbo.fn_calc_weekday('01/01/01','01/08/01')

This function can also be called from stored procedures and other queries just like any other SQL function. Let me know how it goes!

 

[tlbroadbent]

I'm sorry for the misunderstanding. Weekdays between dates has a different meaning to me. What you asked for I refer to as workdays between dates.

The SQL 2000 UDF is a great way to go. Thanks, redlam.

If you are running a version of SQL prior to SQL 2000, I can probably provide some straight SQL code to calculate workdays. Curtrently, the code is in Access 2000 and I will need to convert it. Let me know if you are interested. Terry

;-) The single biggest challenge to learning SQL programming is unlearning procedural programming. -Joe Celko

SQL Article links:

http://members.home.net/tlbroadbent/sqlarticles.htm

 

[e7akerson]

Thanks for the help, all. I am using SQL Server 7.0 but I am trying to get SQL Server 2000. I was hoping that there was some easy date function such as Networkdays in Excel. The less lines of code the better as I am not a natural programmer and I would actually like to understand the code instead of just typing it in and using it for this immediate need. It may sound &quot;goofy&quot; but writing simple SQL makes logical sense.