在 SELECT 中将日期格式化为双周存储桶

Date Format Into BiWeekly Buckets In a SELECT

提问人:Goatfarmer03 提问时间:11/18/2023 最后编辑:Joel CoehoornGoatfarmer03 更新时间:11/20/2023 访问量:106

问:

我有一个数据结构

开始时间 结束时间 BunchOfOtherColumns

我需要创建一个新列,该列添加了一个格式化的日期,该日期落入双周存储桶(周一至周日)。SELECT

例如,对于结果中的每一行,如果日期介于 10 月 2 日至 10 月 15 日之间,则新列将显示 。如果日期介于 10 月 16 日至 10 月 29 日之间,则新列将显示 ,依此类推。EndTime10/15/23EndTime10/29/23

我们可以假设我只会处理 2018 年 10 月 1 日或之后发生的数据,因此这可能是双周周期开始时的零点。第一个双周存储桶为 2018 年 10 月 1 日 - 2018 年 10 月 14 日,标记为 。10/14/2018

如果可能的话,这需要完全包含在语句中,而不是使用 .我只是将一个额外的计算列作为结果集添加到现有表中SELECTGROUP BY

我目前正在使用以下内容,它将格式化为周一至周日的每周存储桶。SELECTEndTime

SELECT
    BunchOfOtherColumns
    ,FORMAT(DATEADD(DAY, 7 - DATEPART(WEEKDAY, EndTime), EndTime), 'MM/dd/yy')
FROM ...

这给了我一个很好的输出

BunchOfOtherColumns 日期桶

但现在我需要双周存储桶

下面是一批示例数据:

开始时间 结束时间 其他列
2023-10-01 10:00:00 2023-10-01 23:59:00
2023-10-02 09:00:00 2023-10-02 12:59:00
2023-10-12 15:00:00 2023-10-12 17:59:00 奈特
2023-10-27 10:00:00 2023-10-27 23:59:00 没有

预期输出

其他列 双周刊
10/01/2023
10/15/2023
奈特 10/15/2023
没有 10/29/2023
sql-server

评论

0赞 Kevin P. 11/18/2023
我想你可以用它来计算这个。DATEDIFF
1赞 Xedni 11/18/2023
如何定义“双周”存储桶?另外,请提供一些示例数据和预期结果。你包含了你的问题的文本描述,这很好,但能够看到你作为输入的具体表格数据,以及你期望在每种情况下的输出是什么,这就是你真正将如何回答你的问题。
0赞 Goatfarmer03 11/18/2023
@Kevin P. Man,相信我,我一直在尝试使用 DATEADD 的荒谬数量的 DATEDIFF,使用天和周,甚至尝试使用19000101开始日期进行校准。我无法让它正确地将它们分成 2 周的部分。
0赞 Goatfarmer03 11/18/2023
@Xedni 双周刊的描述在段落中。10 月 2 日至 10 月 15 日 周一至周日 = 10 月 15 日,以此类推不过,我会在我的 Q 中添加一些表格数据
0赞 Xedni 11/18/2023
给定任何任意日期,您如何知道它是在新的两周周期的第一周还是前一周的第二周?您举了一个具体的例子,说明10月2日是新一周开始的停飞日期。让我们以任意日期 2022-07-11(星期一)为例。这部分是从 2022-07-11 开始到 2022-07-23 结束的两周时间吗?还是在从 2022-07-02 开始到 2022-07-16 结束的时期中间?

答:

0赞 Patrick Hurst 11/18/2023 #1

假设你有某种类型的或列:DATEDATETIME

SET DATEFIRST 1

SELECT i.*, DATEADD(DAY,1-CASE WHEN DATEPART(WEEK,InvoiceDateTimeUTC) % 2 = 0 THEN DATEPART(WEEKDAY,InvoiceDateTimeUTC)
                 ELSE 7+DATEPART(WEEKDAY,InvoiceDateTimeUTC)
            END,CAST(InvoiceDateTimeUTC AS DATE)) AS BiWeeklyStartDate,
            CASE WHEN DATEPART(WEEK,InvoiceDateTimeUTC) % 2 = 0 THEN DATEPART(WEEKDAY,InvoiceDateTimeUTC)
                 ELSE 7+DATEPART(WEEKDAY,InvoiceDateTimeUTC)
            END AS DayNumber
  FROM Invoices i
 ORDER BY CAST(InvoiceDateTimeUTC AS DATE);
发票 ID 发票日期时间UTC 客户 ID 员工 ID 存储 ID BiWeeklyStartDate 双周开始日期 天数
58405 2021-06-06 19:46:15.9633333 241 59 47 2021-05-24 14
59390 2021-06-06 19:46:15.9633333 979 60 15 2021-05-24 14
15356 2021-06-07 19:48:15.9633333 807 16 28 2021-06-07 1
70300 2021-06-07 19:47:15.9633333 21 70 5 2021-06-07 1
24578 2021-06-07 19:48:15.9633333 784 25 57 2021-06-07 1

您可以使用更改模数来调整开始日期,也可以调整周数。SET DATEFIRST

评论

0赞 T N 11/18/2023
使用跨年份界限的日期似乎存在一些连续性问题,在某些情况下从 53 跃升为 1,在其他情况下从 54 跃升为 1。对于大多数年份,53 比 1 的过渡涵盖 7 天,这没关系。对于2005/2006年和2011/2012年等年份,53比1的过渡期为14天。2000/2001 年和 2028/2029 年从 54 过渡到 1,跨度为 7 天,但为偶数/奇数混合。请参阅此 db<>fiddle。一个可能仍然允许基于周的算术的解决方法可能是替换为或类似。DATEPART(WEEK, ...)DATEPART()DATEDIFF(week, 0, date)
1赞 T N 11/18/2023 #2

(如果使用 SQL Server 2022 或更高版本,请参阅 Charlieface 提供的更简单的答案。

给定一个定义任何两周存储桶的第一个星期一的参考日期,您可以计算天数差,然后计算以获取存储桶编号,或计算计算相对于包含两周存储桶的开始星期一的天数。FLOOR(diff / 14.0)((diff % 14) + 14) % 14

需要使用 instead 而不是 just 来处理负偏移。同样,还需要双模输入将负数映射到所需的 0..13 范围。FLOOR(diff / 14.0)diff / 14((diff % 14 + 14) % 14

DECLARE @ReferenceDate DATE = '2023-10-02' -- Reference Monday

;WITH CTE_Dates AS (
    SELECT CONVERT(DATETIME, '2023-09-01 13:45') Dt
    UNION ALL
    SELECT DATEADD(day, 1, D.Dt) AS Dt
    FROM CTE_Dates D
    WHERE D.Dt < '2023-10-31'
)
SELECT
    D.Dt,
    DATENAME(weekday, D.Dt) AS WeekDay,
    DATEDIFF(day, @ReferenceDate, D.Dt) AS DayDiff,
    FLOOR(DATEDIFF(day, @ReferenceDate, D.Dt) / 14.0) AS BucketNo,
    ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14 AS BucketDay,
    CONVERT(DATE, DATEADD(day, - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketStartDt,
    CONVERT(DATE, DATEADD(day, 13 - ((DATEDIFF(day, @ReferenceDate, D.Dt) % 14) + 14) % 14, D.Dt)) AS BucketEndDt
FROM CTE_Dates D
OPTION (MAXRECURSION 1000)

部分结果:

Dt 工作日 日差异 铲斗否 水桶日 BucketStartDt BucketEndDt
2023-10-01 13:45 星期日 -1 -1 13 2023-09-18 2023-10-01
2023-10-02 13:45 星期一 0 0 0 2023-10-02 2023-10-15
2023-10-03 13:45 星期二 1 0 1 2023-10-02 2023-10-15
2023-10-04 13:45 星期三 2 0 2 2023-10-02 2023-10-15
2023-10-05 13:45 星期四 3 0 3 2023-10-02 2023-10-15
2023-10-06 13:45 星期五 4 0 4 2023-10-02 2023-10-15
2023-10-07 13:45 星期六 5 0 5 2023-10-02 2023-10-15
2023-10-08 13:45 星期日 6 0 6 2023-10-02 2023-10-15
2023-10-09 13:45 星期一 7 0 7 2023-10-02 2023-10-15
2023-10-10 13:45 星期二 8 0 8 2023-10-02 2023-10-15
2023-10-11 13:45 星期三 9 0 9 2023-10-02 2023-10-15
2023-10-12 13:45 星期四 10 0 10 2023-10-02 2023-10-15
2023-10-13 13:45 星期五 11 0 11 2023-10-02 2023-10-15
2023-10-14 13:45 星期六 12 0 12 2023-10-02 2023-10-15
2023-10-15 13:45 星期日 13 0 13 2023-10-02 2023-10-15
2023-10-16 13:45 星期一 14 1 0 2023-10-16 2023-10-29
2023-10-17 13:45 星期二 15 1 1 2023-10-16 2023-10-29
2023-10-18 13:45 星期三 16 1 2 2023-10-16 2023-10-29
2023-10-19 13:45 星期四 17 1 3 2023-10-16 2023-10-29

请参阅此 db<>fiddle

原始帖子和随后的评论在哪些星期一构成两周桶的开始或中间方面并不一致,但可以根据需要进行更改以适应最终决定。(它甚至不需要是星期一。@ReferenceDate

评论

1赞 Goatfarmer03 11/22/2023
那个人明白了。感谢您的帮助和调整参考日期的能力,以防万一。
0赞 Kevin P. 11/18/2023 #3

您可以使用简单的算术来计算这一点。

考虑

(DATEADD(DAY, (FLOOR((1 - (DATEDIFF(DAY, '2018-9-30', EndTime) / 14.0 % 1)) * 14)) + 7, EndTime))

SELECT  BunchOfOtherColumns
       ,FORMAT((DATEADD(DAY,(FLOOR((1 - (DATEDIFF(DAY,'2018-9-30',EndTime) / 14.0 % 1)) * 14)) + 7,EndTime)) ,'MM/dd/yy')
FROM ...

我不太明白 10 年 15 月 23 日将如何结束,所以也许我离开了。我认为这将是第 8 个或第 22 个,但出于这个原因,我添加了一个 +7。

评论

0赞 T N 11/18/2023
凯文 - 我认为你需要仔细检查上面的答案。即使初始条件(将 2018-09-30 日期映射到 2018-10-21),它也会失败,远远超出包含的两周存储桶。
2赞 Charlieface 11/19/2023 #4

在 SQL Server 2022 中,只能使用 DATE_BUCKET 函数。

这可以将日期分成几组,您可以为其指定分段的类型和大小以及来源。

似乎与您所说的相反,为了让水桶按照您所说的方式掉出来,您需要将原点移动到 .20180924

DATE_BUCKET(week, 2, EndTime, CAST('20180924' AS datetime))

要获得存储桶的末尾,只需添加 13 天

DATEADD(day, 13, DATE_BUCKET(week, 2, EndTime, CAST('20180924' AS datetime)))

db<>小提琴

评论

0赞 T N 11/20/2023
最好的答案总是:“有一个功能”。很棒的发现。比所有其他答案中的各种棘手计算要简单得多。
0赞 Goatfarmer03 11/20/2023
我希望我有 2022 年,但我不相信我有。早上会办理入住手续。我的错没有指定,谢谢你的回答
0赞 Goatfarmer03 11/22/2023
不,它是说这不是一个可用的内置。不过很感激。它提供了在 SSMS 中逐步执行它的帮助,但如果我运行查询,则会用红线划线。
0赞 Charlieface 11/22/2023
SELECT @@version;会告诉你你的版本号。SSMS 有时会对语法感到困惑,尤其是在未使用最新版本的 SSMS 时。
0赞 Goatfarmer03 11/22/2023
SQL Server 2019 RTM。不过,感谢您的命令,因为我主要从 SSMS pov 中工作,因此缺少很多技能