Geek Answers Handbook

Calculating the nearest future recurring payment date in SQL

I’m working on a recurring payment application. Payments are made once every two weeks.

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

and now I need an SQL statement that can calculate the next next payment date from the specified date in the WHERE clause

i.e. SELECT ... FROM ... WHERE someDate <[CALCULATION OF THE NEXT BOARD DATE FROM THIS DATE]

If I did 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());

the conclusion will be

 15/06/2009 12:00:00 am 29/06/2009 12:00:00 am

but I am completely stuck when I need to do this in SQL.

Can anybody help me? I am using SQL Server 2005

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

+4
source share
3 answers

In order to perform the calculation correctly, you will need what I would call a reference date, for example. The date you start the two-week cycle. (there is a firstEverPayment declaration in your code)

Given that you can specify the number of days between them and the link to get the number of days. Divide by 14, but round with Floor (for example, find out how much 2 weeks have passed) Add 1 to go forward to the two-week interval. (You can skip adding 1 using the ceiling rather than the floor) Multiply by 14 to get the day score. Use the Add date to add those days.

Sort of

select dateadd (dd, (Ceiling (datif (dd, '1/1/09', getdate ()) / 14) * 14), '1/1/09')

Where I used 1/1/09 as a key date.

+3
source

how about something like that. Grab the current day of the year, divide it by 14 to get the remainder, and add the difference from 14 to your date. You may need to adjust DaysOfYear according to 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)
+2
source

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

This will find you the next payment date after @FromDate with the starting date @StartDate .

0
source

More articles:


All Articles