I am focusing on a credit card applicatoin that are responsible for periodic obligations Obligations are carried out fortnightly i.e.

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

and today I want a SQL statement that may calculate the nearest next payment date from the given date within the WHERE clause

i.e. Choose ... FROM ... WHERE someDate < [CALCULATE NEXT PAY DATE From The GIVEN DATE]

Basically were to get this done in C# I'd 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;

Therefore if I actually do

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

output is going to be

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

but I am totally stuck when I must do that in SQL.

Can anybody produce a hands about this? I'm using SQL Server 2005

UPDATE: Incidentally, I didn't remember to say that last payment date isn't obtainable in database, it needs to be calculated at runtime.

To complete the calculation correctly you'll need things i would describe as the reference date e.g. the date that you begin the two week cycle from. (inside your code thats the firstEverPayment declaration)

Given that you could datediff the amount of days between now and also the reference to obtain the length of time. Divide by 14, but round lower using Floor (e.g. exercise the number of 2 week times have previously happened) Add 1 - to maneuver forward a 2 week interval. (You are able to skip the add 1 by utilizing Ceiling, not floor) Multiply by 14 - to obtain the day count Use Date Increase add individuals days.

Something similar to

choose dateadd(dd, (Ceiling(datediff (dd, '1/1/09', getdate()) /14) * 14), '1/1/09')

Where I made use of 1/1/09 because the reference date.

what about something similar to this. Grab the present day of the season, divide by 14 to obtain the remainder, and add the main difference from 14 to your date. You might want to adjust the DaysOfYear to fit your first payment of the season...

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)

Use [cde]!


Which will find the next pay date after create procedure GetNextDate ( @StartDate datetime, @FromDate datetime ) as begin select dateadd(day, 14*cast(datediff(day, @StartDate, @FromDate) / 14 + 1 as int), @StartDate) end , having a beginning date of @FromDate.