Geek Answers Handbook

A month has passed?

In my database, I have a table containing some subscription information. I have among others StartDate and EndDate as DateTime.

I need the Linq request to receive all rows for payment. Payment should be made every month on the same day when they registered (StartDate), and stop at EndDate. Therefore, if they are registered on May 23, I will need to set them again on 23. June, 23. July, etc.

var query = from c in db.Subscription where c.StartDate.Value.Day == DateTime.Now.Day // What if today is Feb. 28 and a customer registered January 31. // What if....

I lost ... please help!

Best, Jon 2H

+4
source share
5 answers

Why don't you create a separate table for all payments.

When the new subscription is displayed, you will need to calculate all future payment dates for this subscription and add the number of rows to the DuePayments table using SubscriptionID, PaymentDate and Amount.

The number of rows will be equal to the number of months between the start date of the subscription and the end date, and payment dates can be easily calculated using DateTime.AddMonths (1), but less than the end date.

+2
source

One way to handle polling days of a month (if you want to bill on the last day of the month in an odd case):

 var Tomorrow = DateTime.Today.AddDays(1); var query = from c in db.Subscription where c.EndDate.Value > DateTime.Today && (c.StartDate.Value.Day == DateTime.Today.Day || (Tomorrow.Month > DateTime.Today.Month && c.StartDate.Value.Day > DateTime.Today.Day)) select c;

You may need to create a new table for payment and calculate dates up, but. Thus, you can track when payments will be made, as well as make life easier in the future.

+3
source 
 select * from Subscription s where getdate() > dateadd(month, 1, isnull(s.lastBilledDate, s.StartDate) )

Something like this should work. We hope you have a field in the database somewhere that shows the last billing date, which will be better used than the start date of the subscription.

+1
source

There is an AddMonths method in DateTime structures.

http://msdn.microsoft.com/en-us/library/system.datetime.addmonths.aspx

Yes, I probably thought about the difference of one month.

How about capturing strings where StartDate.Value.Day == DateTime.Now.Day or (StartDate.Value.Day> DateTime.Now.Day and DateTime.Now.Day - the last day of the month, regardless of the method for this would be) .

0
source

Your problem is not only related to programming, but rather related to business. Suppose I don’t even have a computer and software for this project / product, etc.

What if the plan starts on January 31st? Will I pay them on February 31? Not. Then when? How can i calculate?

It would be best to set the exact number of days (say 30) and use this as a business model. On the 30th day after the launch of the service, you will be billed. Or something like that.

In T-SQL and .NET, adding 30 days to DateTime doesn't really matter:

.NET:

 DateTime value = DateTime.Now; DateTime billTime = value.AddDays(30);

T-SQL:

 DATEADD(DAY,30,value)
0
source

More articles:


All Articles