MySQL duration between dates

[macman1]

I am in the middle of building a web application for work and have hit a road block that I am hoping you can help me with. I am building a recurring billing scheduler that will display projects that need to be billed every 30 days. I have two columns in my table; "duration_start" and "duration_finish". They are stored in MySQL's "DATE" format. What I need to have happen is have the records show up from the duration_start for the month until a button on my form called billed is clicked. This will set a field in my table called billed to 1. That will make the record okay for another 30 days until the duration_finish is reached. For instance, if I have a record whose duration_start date is 2006-06-07 and duration_finish date is 2006-10-06 I need to have the record show up for the month of July, August, September and October. Does this make sense? I would appreciate any help anyone can give me on this.

[txbakers]

I'm not entirely clear on your requirements, but I think you could write a routine based on number of days using the mySQL function TO_DAYS.

I need to keep track of whether a certain account is within a 60 day trial period, and I include this line in the query:

WHERE auth='N' and 60 - (TO_DAYS(NOW()) - TO_DAYS(regdate)) > 0

which checkes the registration date, and subtracts the to_days until NOW, then from 60 and compares that to 0. This brings me back records that are within 60 days of the registration date.

Dates are a nasty business.

[macman1]

Hi txbakers,

Thanks for your reply. I had to laugh when you said "Dates are a nasty business" because it's true. This is the second time in the past few weeks that I've had trouble figuring out something with dates!

I modified the query you posted to suit my needs but it still doesn't seem to do what I want. If you're familiar with the financial world, what I am trying to do would basically represent "NET 30" billing. Where every thirty days between the duration_start and duration_finish relevant records would appear waiting to be billed if now() falls between the two dates and every 30 days between the two dates.

Thanks for your help!
Steve