How can I calculate the number of work days between two dates from table (from the 1st row to the end) in SQL Server 2008?
I tried something like this, but it does not work
DECLARE @StartDate as DATETIME, @EndDate as DATETIME
Select @StartDate = date2 from testtable ;
select @EndDate = date1 from testtable ;
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 Can simply use datediff function of sql. and then you can subtract weekends between those dates if any. For example check below query. You also can calculate holidays between start/end date and can subtract that from final selection.
I would always recommend a Calendar table, then you can simply use:
Since SQL has no knowledge of national holidays for example the number of weekdays between two dates does not always represent the number of working days. This is why a calendar table is a must for most databases. They do not take a lot of memory and simplify a lot of queries.
But if this is not an option then you can generate a table of dates relatively easily on the fly and use this
EDIT
If you need to calculate the difference between two date columns you can still use your calendar table as so:
Example on SQL-Fiddle
Interesting question. It is always important to understand the use case. If counting from Sunday to Monday, would we want to say there is one day as if we had until close of business on Monday. Or would we want to say there are no days as if there were no days before Monday began. In our case we needed to count both days (start and end) if they were weekdays, because I was working in a payroll application estimating some accruals. And any day weekend days would later be accounted for by holiday, and overtime records.
When I pulled out my calendar I realized that when counting from Saturday or Sunday we could just start counting from Monday. And also when counting to a Saturday or Sunday I could just stop counting when we got to Friday. So I wrote a function that adjusted the starting and ending dates, Found the number of weeks by dividing by 7, multiplied by that 5 weekdays per week and then added back the remainder. I did have to account for the case when we started counting on a weekend but never got to a Monday.
-- ============================================= -- Author: Todd P Payne -- Create date: 9/1/2018 -- Description: Counts number of weekdays between two dates -- Unlike DateDiff StartDate and EndDate are inclusive -- FROM Monday, Jan 1 to Monday Jan 1 will return 1 -- ============================================= CREATE FUNCTION [dbo].[ufnCountWeekdays] ( -- Add the parameters for the function here @StartDate DateTime, @EndDate DateTime ) RETURNS INT AS BEGIN -- Declare the return variable here DECLARE @CountofWeekDays INT = NULL; DECLARE @TempDate DateTime;
END GO
Happy Coding
Todd P Payne
This does it excluding the days out but date part rather than description. You can substitute the parameters used as an example for the values in your query.
To add to GarethD's answer - I put together the SQL for a USA version of the Calendar table, with all holidays and weekends set to is_working_day = false... for anyone that would like the SQL, here it is: