Modulo arithmetic on dates in SQL

Question

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:

Table: Pattern

ID | BaseDate   | DayCount
-----------------------------
1  | 01/02/2005 | 7

Table: 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 PatternDetail table for a given working pattern. In pseudocode I would do:

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?

Answer 1

Use DATEDIFF to get the day count different. This gives an integer. Then use % (standard SQL modulo operator).

Is is that simple?

Answer 2

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:

@Offset = ((DATEDIFF(day, @BaseDate, GETDATE()) % @DayCount)