#sql #sql-server #sql-server-2008
#sql #sql-server #sql-server-2008
Вопрос:
У меня есть две даты, 2019-01-02 12:33:36.000
и 2019-01-09 19:05:18.000
я хочу рассчитать часы и минуты между двумя, исключая субботу и воскресенье. Я могу вычислить разницу, но не уверен, как я могу исключить субботу и воскресенье из расчета.
Исключать рабочее время нельзя, нужно исключить только с 12:00 до 12:00 с субботы по воскресенье, и я использую SQL Server 2008.
Я использую SQL Server 2008
Edit -- From the comments suggestions I have this to calculate weekends
..DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-01-02 12:33:36.000'
SET @EndDate = '2019-01-09 19:05:18.000'
SELECT
(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)
Теперь я просто вычислю минуты и вычту из общего числа.
Комментарии:
1. Подсчитайте количество суббот и воскресений между этими двумя датами, а затем вычтите это число * 24 из вашего результата.
2. Покажите свою работу, которую вы проделали на данный момент. Тогда мы будем рады «исправить» это для вас. 🙂
3. Спасибо, Дэвид.. Хорошая идея
4. Как насчет праздников? Возможно, вам было бы лучше создать таблицу дат для использования в качестве подстановки для выходных и праздничных дней.
Ответ №1:
Итак, после получения нескольких предложений от Дэвида, вот мой окончательный код, который дает мне минуты между двумя датами, исключая выходные
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-03-07 00:00:00.000'
SET @EndDate = '2019-03-11 23:59:59.000'
Declare @TotalMins int
Declare @Weekends int
Declare @FinalMinutes int
Set @TotalMins = DATEDIFF(MINUTE, @StartDate,@EndDate);
Set @Weekends =
(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)
Set @FinalMinutes = @TotalMins - (@Weekends * 24 * 60)
Select @FinalMinutes
Спасибо
Комментарии:
1. Поскольку в конечной дате не хватает 1 секунды, это округляет количество часов, так что в итоге получается 4319 часов за 3 дня, когда на самом деле за 3 дня 4320 часов.
Ответ №2:
Используйте функцию для возврата таблицы дат / времени для каждой минуты между двумя датами, затем исключите выходные из результата и преобразуйте количество в часы и минуты;
DECLARE @StartDate DATETIME
DECLARE @EndDate DATETIME
SET @StartDate = '2019-03-07 00:00:00.000'
SET @EndDate = '2019-03-11 23:59:59.000'
SELECT COUNT([Start_Date])/60 as 'Hours'
, COUNT([Start_Date])%60 as 'Minutes'
FROM dbo.generateDateTable(@StartDate, @EndDate, 'minute',1)
WHERE DATENAME(DW, [Start_Date]) NOT IN ('Saturday','Sunday')
Удобная функция GenerateDateTable;
CREATE function [dbo].[generateDateTable]
(
@start_date datetime
, @end_date datetime
, @datepart varchar(20) = 'day'
, @step int = 1
)
returns @dates table
(
start_date datetime,
end_date datetime
)
as
begin
if( @datepart in ('year', 'yy', 'yyyy', 'quarter', 'qq', 'q', 'month', 'mm', 'm',
'dayofyear', 'dy', 'y', 'day', 'dd', 'd', 'week', 'wk', 'ww') )
begin
set @start_date = cast(floor(cast(@start_date as float)) as datetime)
set @end_date = cast(floor(cast(@end_date as float)) as datetime)
end
declare @new_start datetime
while @start_date <= @end_date
begin
set @new_start = (case
when @datepart in ('year', 'yy', 'yyyy') then dateadd(yy, @step, @start_date)
when @datepart in ('quarter', 'qq', 'q') then dateadd(qq, @step, @start_date)
when @datepart in ('month', 'mm', 'm') then dateadd(mm, @step, @start_date)
when @datepart in ('dayofyear', 'dy', 'y') then dateadd(dy, @step,
@start_date)
when @datepart in ('day', 'dd', 'd') then dateadd(dd, @step, @start_date)
when @datepart in ('week', 'wk', 'ww') then dateadd(ww, @step, @start_date)
when @datepart in ('hour', 'hh') then dateadd(hh, @step, @start_date)
when @datepart in ('minute', 'mi', 'n') then dateadd(n, @step, @start_date)
when @datepart in ('second', 'ss', 's') then dateadd(s, @step, @start_date)
when @datepart in ('millisecond', 'ms') then dateadd(ms, @step, @start_date)
else dateadd(dd, @step, @start_date)
end)
insert
@dates
(
start_date
, end_date
) values (
@start_date
, dateadd(ms, -3, @new_start)
)
set @start_date = @new_start
end
return
end