Calculating the closest future periodic payment date in SQL

Question

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 1

Use dateadd!

create procedure GetNextDate
(
    @StartDate datetime,
    @FromDate datetime
) as
begin
    select 
        dateadd(day, 
            14*cast(datediff(day, @StartDate, @FromDate) / 14 + 1 as int), 
        @StartDate)
end

That will find you the next pay date after @FromDate, with a starting date of @StartDate.

Answer 2

how about something like this. Grab the current day of the year, divide by 14 to get the remainder, and add the difference from 14 back to your date. You may have to adjust the DaysOfYear to match your first payment of the year...

declare @mydate datetime
set @mydate = '20090607'
select  DATEADD(dd, 14 - DATEPART(dayofyear, @mydate) % 14, @mydate)


set @mydate = '20090611'
select  DATEADD(dd, 14 - DATEPART(dayofyear, @mydate) % 14, @mydate)


set @mydate = '20090612'
select  DATEADD(dd, 14 - DATEPART(dayofyear, @mydate) % 14, @mydate)

set @mydate = '20090617'
select  DATEADD(dd, 14 - DATEPART(dayofyear, @mydate) % 14, @mydate)

Answer 3

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.