Calculating the closest future periodic payment date in SQL

I’m working on an application that deals with periodic payments Payments are done fortnightly i.e.

• payment 1: 2009-06-01
• payment 2: 2009-06-15
• payment 3: 2009-06-29

and now I need a SQL statement that can calculate the closest next payment date from a given date in the WHERE clause

i.e. SELECT … FROM … WHERE someDate < [CALCULATE NEXT PAY DATE FROM A GIVEN DATE]

If I were to do this in C# I would go

static DateTime CalculateNextPayDateFrom(DateTime fromDate)
{
    var firstEverPayment = new DateTime(2009, 6, 1);
    var nextPayment = firstEverPayment;

    while (nextPayment < fromDate)
    {
        nextPayment += new TimeSpan(14, 0, 0, 0);
    }

    return nextPayment;
}

So if I do

Console.WriteLine(CalculateNextPayDateFrom(new DateTime(2009, 6, 12)).ToString());
Console.WriteLine(CalculateNextPayDateFrom(new DateTime(2009, 6, 20)).ToString());

output will be

15/06/2009 12:00:00 a.m.
29/06/2009 12:00:00 a.m.

but I’m totally stuck when I have to do this in SQL.

Can anyone give me a hand on this? I am using SQL Server 2005

UPDATE: By the way, I forgot to mention that last payment date is not available in database, it has to be calculated at runtime.

Answer

To do the calculation properly you need what I would refer to as the reference date e.g. the date from which you start the 2 week cycle from. (in your code thats the firstEverPayment declaration)

Given that you can datediff the number of days between now and the reference to get the number of days. Divide by 14, but round down using Floor (e.g. work out how many 2 week intervals have already occured) Add 1 – to move forward a two week interval. (You can skip the add 1 by using Ceiling, not floor) Multiply by 14 – to get the day count Use Date Add to add those days.

Something like

select dateadd(dd, (Ceiling(datediff (dd, ‘1/1/09’, getdate()) /14) * 14), ‘1/1/09’)

Where I used 1/1/09 as the reference date.