Z
Z
zhaar2019-10-02 11:12:32
SQL Server
zhaar, 2019-10-02 11:12:32

How to find out if a specific date falls within a period with a given frequency?

There are 2 columns of data, one contains the start date and the second contains the recurrence period. How to correctly find whether the current date fits the number that will result from its periodic repetition? The table is rather big, on the order of several hundred thousand rows.
Here is an example:
StartDate: 20181116
Period: 14
The easiest way is to calculate the difference in dates and if the number is round, then the period is correct:
select datediff(day,'20181116','20190906')/convert(real,14 ) = 21
If non-integer then:
select datediff(day,'20181116','20190906')/convert(real,15) = 19.6
But in such a scheme, it is not clear to me how to check that the result is an integer? Isnumeric is not suitable here, if you divide without converting the number, then I will always get an int ...
Maybe there is some other method that will immediately say that the date corresponds to the period?

Answer the question

In order to leave comments, you need to log in

1 answer(s)
K
Konstantin Tsvetkov, 2019-10-02
@zhaar

SELECT DATEDIFF( DAY, '20181116', '20190906') % 14
SELECT DATEDIFF( DAY, '20181116', '20190906') % 15

Didn't find what you were looking for?

Ask your question

Ask a Question

731 491 924 answers to any question