检查日期范围是否有周末-Java 学习之路

如果我有2个日期，我知道我可以使用 datediff 计算两个日期之间的天数，小时数，分钟数等，例如：

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-15';

select datediff( hour, @start, @end );

如何确定日期范围是否包含周末？

我想知道日期范围是否包括周末的原因是因为我想从日或小时数中减去周末 . 即如果开始日是星期五，结束日期是星期一，我应该只有1天或24小时 .

Datepart 1 = Sunday，而datepart 7 = Saturday在我的服务器上 .

6 回答

我有一个函数来计算两个日期之间的工作日，基本查询是

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-17';
SELECT 
   (DATEDIFF(dd, @Start, @end) +1)  -- total number of days (inclusive)
  -(DATEDIFF(wk, @Start, @end) * 2) -- number of complete weekends in period
  -- remove partial weekend days, ie if starts on sunday or ends on saturday
  -(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END) 
  -(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END)

因此，如果日期包括周末，如果工作日与日期不同，则可以计算出来

SELECT case when  (DATEDIFF(dd, @Start, @end) +1) <>
   (DATEDIFF(dd, @Start, @end) +1)  -- total number of days (inclusive)
  -(DATEDIFF(wk, @Start, @end) * 2) -- number of complete weekends in period
  -- remove partial weekend days, ie if starts on sunday or ends on saturday
  -(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END) 
  -(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END) then 'Yes' else 'No' end as IncludesWeekends

或者更简单

SELECT   (DATEDIFF(wk, @Start, @end) * 2) +(CASE WHEN DATENAME(dw, @Start) = 'Sunday' THEN 1 ELSE 0 END)      +(CASE WHEN DATENAME(dw, @end) = 'Saturday' THEN 1 ELSE 0 END)  as weekendDays

回复于 2024-05-07T23:33:26+08:00

如果满足以下三个条件中的任何一个条件，那么您将有一个周末：

结束日期的星期几（以整数形式）小于开始日期的星期几
任何一天本身都是周末
该范围至少包括六天

select 
    Coalesce(
    --rule 1
    case when datepart(dw,@end) - datepart(dw,@start) < 0 then 'Weekend' else null end,
    -- rule 2
    -- depends on server rules for when the week starts
    -- I think this code uses sql server defaults
    case when datepart(dw,@end) in (1,7) or datepart(dw,@start) in (1,7) then 'Weekend' else null end,
    --rule 3
    -- six days is long enough
    case when datediff(d, @start, @end) >= 6 then 'Weekend' Else null end,
    -- default
    'Weekday')

回复于 2024-05-07T23:33:26+08:00

一种方法，只是向您展示如何使用数字表格

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-15'; -- play around by making this 2013-06-14 and other dates


IF EXISTS (SELECT * FROM(
SELECT DATEADD(dd,number,@start) AS SomeDAte 
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd,number,@start) BETWEEN @start AND @end) x
WHERE DATEPART(dw,SomeDate) IN(1,7))  -- US assumed here
SELECT 'Yes'
ELSE
SELECT 'No'

返回两个日期之间所有周末的示例

declare @start datetime;
set @start = '2013-06-14';

declare @end datetime;
set @end = '2013-06-30';



SELECT DATEADD(dd,number,@start) AS SomeDAte 
FROM master..spt_values
WHERE type = 'P'
AND DATEADD(dd,number,@start) BETWEEN @start AND @end
AND DATEPART(dw,DATEADD(dd,number,@start)) IN(1,7)

结果

2013-06-15 00:00:00.000
2013-06-16 00:00:00.000
2013-06-22 00:00:00.000
2013-06-23 00:00:00.000
2013-06-29 00:00:00.000
2013-06-30 00:00:00.000

回复于 2024-05-07T23:33:26+08:00

您可以使用以下功能 . 如果它在周末开始，则第一个将给定的开始或结束时间移动到星期一（周五，如果倒退） . 第二个计算没有周末的两个日期之间的秒数 . 然后你只需要检查总天数是否等于没有weksends的天数（下面的演示） .

CREATE FUNCTION [dbo].[__CorrectDate](
    @date DATETIME,
    @forward INT
)

RETURNS DATETIME AS BEGIN
    IF (DATEPART(dw, @date) > 5) BEGIN

        IF (@forward = 1) BEGIN
            SET @date = @date + (8 - DATEPART(dw, @date))
            SET @date = DateAdd(Hour, (8 - DatePart(Hour, @date)), @date)
        END ELSE BEGIN
            SET @date = @date - (DATEPART(dw, @date)- 5)
            SET @date = DateAdd(Hour, (18 - DatePart(Hour, @date)), @date)
        END
        SET @date = DateAdd(Minute, -DatePart(Minute, @date), @date)
        SET @date = DateAdd(Second, -DatePart(Second, @date), @date)
    END

    RETURN @date
END

GO

CREATE FUNCTION [dbo].[__DateDiff_NoWeekends](
    @date1 DATETIME,
    @date2 DATETIME
)

RETURNS INT AS BEGIN
    DECLARE @retValue INT

    SET @date1 = dbo.__CorrectDate(@date1, 1)
    SET @date2 = dbo.__CorrectDate(@date2, 0)

    IF (@date1 >= @date2)
        SET @retValue = 0
    ELSE BEGIN
        DECLARE @days INT, @weekday INT
        SET @days = DATEDIFF(d, @date1, @date2)
        SET @weekday = DATEPART(dw, @date1) - 1

        SET @retValue = DATEDIFF(s, @date1, @date2) - 2 * 24 * 3600 * ((@days + @weekday) / 7) 
    END

    RETURN @retValue
END

然后你可以这样得到信息：

declare @start datetime
set @start = '20130614'

declare @end datetime
set @end = '20130615'

declare @daysTotal int
declare @daysWoWeekends int

SET @daysTotal = DATEDIFF(dd, @start, @end)
SET @daysWoWeekends = dbo.__DateDiff_NoWeekends(@start, @end) / (24 * 3600)

SELECT CASE WHEN @daysTotal = @daysWoWeekends
       THEN 'No weekend between'
       ELSE 'There are weeksends' END,
       @daysTotal,
       @daysWoWeekends,@start,@end

这是 demo ：http://sqlfiddle.com/#!6/7cda7/11

There are weeksends 1   0   June, 14 2013 00:00:00+0000 June, 15 2013 00:00:00+0000

回复于 2024-05-07T23:33:26+08:00

这是简单和通用的查询 . 你可以通过递归查询来实现结果 . 检查以下查询

with mycte as
(
  select cast('2013-06-14' as datetime) DateValue
  union all
  select DateValue + 1 from mycte where DateValue + 1 < '2013-06-17'
)

select count(*) as days , count(*)*24 as hours
from    mycte
WHERE DATENAME(weekday ,DateValue) != 'SATURDAY' AND 
DATENAME(weekday ,DateValue) != 'SUNDAY'
OPTION (MAXRECURSION 0)

它一定会对你有用 .

回复于 2024-05-07T23:33:26+08:00

您可以使用递归CTE来获取范围之间的日期

WITH    CTE_DatesTable
          AS ( SELECT   @MinDate AS [EffectiveDate]
               UNION ALL
               SELECT   DATEADD(dd, 1, [EffectiveDate])
               FROM     CTE_DatesTable
               WHERE    DATEADD(dd, 1, [EffectiveDate]) <= @MaxDate )

            SELECT  [EffectiveDate]
            FROM    CTE_DatesTable
    OPTION  ( MAXRECURSION 0 );

然后使用..过滤掉周末

((DATEPART(dw, DT.EffectiveDate) + @@DATEFIRST) % 7) NOT IN (0, 1)

回复于 2024-05-07T23:33:26+08:00

检查日期范围是否有周末

6 回答

相关问题