oracle SQL 中日期的模运算
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/673651/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Modulo arithmetic on dates in SQL
提问by Mark Pim
I have a system which defines repeating patterns of days. Each pattern has a base date (often a few years in the past, when the pattern was created) and a day count (which loops), so for example it might define a pattern for a seven day period:
我有一个定义重复天数模式的系统。每个模式都有一个基准日期（通常是过去几年，模式创建的时间）和一个天数（循环），例如它可以定义一个 7 天周期的模式：
Table: Pattern
表：图案
ID | BaseDate | DayCount
-----------------------------
1 | 01/02/2005 | 7
Table: PatternDetail
表：PatternDetail
PID | Offset | Detail
----------------------
1 | 0 | A
1 | 1 | B
1 | 2 | B
1 | 3 | C
etc.
(The detail column is domain specific and not relevant.)
（详细信息列是特定于域的，不相关。）
What I want to do is, given a date (say today) work out the correct Offset in the PatternDetailtable for a given working pattern. In pseudocode I would do:
我想要做的是，给定一个日期（比如今天），在PatternDetail表中为给定的工作模式计算出正确的偏移量。在伪代码中，我会这样做：
offset = ((today.InDays) - (Pattern.BaseDate.InDays)) % (Pattern.DayCount)
How can I do this in SQL (needs to work in MSSQL Server and Oracle)? In other words how can I calculate the number of days between two dates and take the modulus of this difference?
如何在 SQL 中执行此操作（需要在 MSSQL Server 和 Oracle 中工作）？换句话说，我如何计算两个日期之间的天数并取这种差异的模数？
回答by Daniel Pratt
I don't know what is available in PL/SQL, but T-SQL has a DATEDIFF function which appears to be what you're looking for:
我不知道 PL/SQL 中有什么可用，但 T-SQL 有一个 DATEDIFF 函数，它似乎是您正在寻找的：
@Offset = ((DATEDIFF(day, @BaseDate, GETDATE()) % @DayCount)
回答by gbn
Use DATEDIFF to get the day count different. This gives an integer. Then use % (standard SQL modulo operator).
使用 DATEDIFF 获取不同的天数。这给出了一个整数。然后使用 %（标准 SQL 模运算符）。
Is is that simple?
有那么简单吗？
