在 SQL Server 中计算排除周末(周一至周五)的天数 [英] Calculating days to excluding weekends (Monday to Friday) in SQL Server
问题描述
如何计算 SQL Server 2008 中表(从第一行到最后)的两个日期之间的工作日数?
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)
推荐答案
我总是推荐 日历表,那么你可以简单地使用:
I would always recommend a Calendar table, then you can simply use:
SELECT COUNT(*)
FROM dbo.CalendarTable
WHERE IsWorkingDay = 1
AND [Date] > @StartDate
AND [Date] <= @EndDate;
由于 SQL 不了解国定假日,例如,两个日期之间的工作日数并不总是代表工作日数.这就是为什么大多数数据库都必须使用日历表的原因.它们不占用大量内存并简化了大量查询.
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
SET DATEFIRST 1;
DECLARE @StartDate DATETIME = '20131103',
@EndDate DATETIME = '20131104';
-- GENERATE A LIST OF ALL DATES BETWEEN THE START DATE AND THE END DATE
WITH AllDates AS
( SELECT TOP (DATEDIFF(DAY, @StartDate, @EndDate))
D = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.Object_ID), @StartDate)
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
)
SELECT WeekDays = COUNT(*)
FROM AllDates
WHERE DATEPART(WEEKDAY, D) NOT IN (6, 7);
<小时>
编辑
如果您需要计算两个日期列之间的差异,您仍然可以像这样使用日历表:
If you need to calculate the difference between two date columns you can still use your calendar table as so:
SELECT t.ID,
t.Date1,
t.Date2,
WorkingDays = COUNT(c.DateKey)
FROM TestTable t
LEFT JOIN dbo.Calendar c
ON c.DateKey >= t.Date1
AND c.DateKey < t.Date2
AND c.IsWorkingDay = 1
GROUP BY t.ID, t.Date1, t.Date2;
这篇关于在 SQL Server 中计算排除周末(周一至周五)的天数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!