datetime - SQL query to match whole number of weeks -

i setting recurring billing processor*. client wants able set clients can choose billing occur fortnightly, aka every 2 weeks.

i attempt calculate 'next' payment date myself, seems more reliable record first payment date, , use sql query calculate 'is today 2 n weeks after first date'.

select * recurring_payments (datediff(first_billing_date, now() % 14) = 0 

however has downside of doing (presumably) unindexable full table scan. there better way of finding rows field matches 2n days ago?

this in mysql initially, moving mssql in few weeks, generic method better bespoke feature.

*yes, i'm scared.

so, said, can add indicator numbers 1 14 indicate payment "cycle".

so have 14 cycles , have retrieve in today's cycle...8 in example, september 9.

sql fiddle

mysql 5.5.32 schema setup:

create table recurring_payments     (`clientid` int, `first_billing_date` date, `cycle` int) ;  insert recurring_payments     (`clientid`, `first_billing_date`, `cycle`) values     (1, '2014-08-19', 1),     (2, '2014-08-21', 3),     (3, '2014-08-23', 5),     (4, '2014-08-25', 7),     (5, '2014-08-26', 8),     (6, '2014-08-27', 9),     (7, '2014-08-29', 11),     (8, '2014-08-30', 12),     (9, '2014-09-01', 14),     (10, '2014-09-02', 1),     (11, '2014-09-03', 2),     (12, '2014-09-05', 4),     (13, '2014-09-06', 5),     (14, '2014-09-07', 6),     (15, '2014-09-08', 7) ; 

query 1:

select * recurring_payments cycle = 8 , first_billing_date < curdate() 

results:

| clientid |            first_billing_date | cycle | |----------|-------------------------------|-------| |        5 | august, 26 2014 00:00:00+0000 |     8 |