?
Solved

MYSQL PHP Query Date 18 months

Posted on 2011-04-28
13
Medium Priority
?
375 Views
Last Modified: 2012-05-11
Hi Experts,

The Query attached currently gets dates 90 days in prior to contract end date.

Is there a way of modifying this query so it shows me which customers are 18 months INTO their contract.

Im guessing would be something like

$sql = "SELECT customer_name, account_number, mobile_number, start_contract
FROM customer_details WHERE STR_TO_DATE(start_contract, '%d.%m.%Y') < CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 550DAY)
 ORDER BY customer_name ASC ";

Or something like that?

Thanks
$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details WHERE STR_TO_DATE(end_contract, '%d.%m.%Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 365 DAY)
 ORDER BY end_contract ASC ";

Open in new window

0
Comment
Question by:NeoAshura
  • 6
  • 3
  • 3
  • +1
13 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 35482931
your text and your query are not using the same numbers .... 90 vs 364 vs 550 ... not very consistent ...

note that date_add also accepts MONTH and YEAR as interval specification.

please clarify the request
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35482959
Ahh i see well needs to show customers that are 18 months INTO their contracts. (coming up to the last 6 months of their contract) So surely if i just changed the 90 day to 6 months this should show the customers comping up to their last 6 months. hmm let me have a fiddle.
0
 
LVL 111

Accepted Solution

by:
Ray Paseur earned 1000 total points
ID: 35483127
Interesting data base design... Wouldn't it make sense to have the contract end date in the table as well as the start date?  If all you have is a start date, you can only write contracts of a single specific length.  As a businessman I would not want to hear my software developers tell me that I could not offer different term contracts!

Then if you have the end date, you can just query for end dates that fall anywhere in the next six months.   The end dates you want would be greater than date('c') and less than date('c', strtotime('+6 months')).  Easy!
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 6

Author Comment

by:NeoAshura
ID: 35483153
hmm now i get

Warning: mail() [function.mail]: SMTP server response: 420 Internal error: Max line length exceeded. in G:\EasyPHP-5.3.6.0\www\sendmail2.php on line 38

Ive tried changing my settings in PHP.INI but still didnt work.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35483164
yeah it has the start date and end date in the same column ray. What do you surgest i try sorry, i didnt quite understand your explanation.
0
 
LVL 4

Assisted Solution

by:msk_apk
msk_apk earned 1000 total points
ID: 35483398
$sql = "SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details WHERE STR_TO_DATE(end_contract, '%d.%m.%Y') BETWEEN CURDATE() AND DATE_ADD(CURDATE(), INTERVAL 365 DAY)
 ORDER BY end_contract ASC ";

if end_contract is the date at which contract is going to end, then above query lists all the customers  for whom the contract is going to end in the next 1 year.

SELECT customer_name, account_number, mobile_number, end_contract
FROM customer_details WHERE DATE_DIFF(CURDATE(), STR_TO_DATE(start_contract, '%d.%m.%Y')) > 550
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35483434
the start date and end date in the same column -- Wow, how did that happen?  I don't know what to say.  Maybe put these dates into two different columns?  You can use ALTER TABLE to add columns, then you can write a custom query to take the existing column apart and move the information into the new columns.  I would certainly do that before I did anything else with this table.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35483454
Sorry, Im dyslexic and type to fast for my own good, let me try that one again..

contract start date and contract end date both have their separate columns.

@msk apk: thanks for your reply i tried the query at the bottom. and got the following error

FUNCTION newdatabase.DATE_DIFF does not exist IN SELECT customer_name, account_number, mobile_number, end_contract FROM customer_details WHERE DATE_DIFF(CURDATE(), STR_TO_DATE(start_contract, '%d.%m.%Y')) > 550 ORDER BY end_contract ASC
0
 
LVL 111

Expert Comment

by:Ray Paseur
ID: 35483500
contract start date and contract end date both have their separate columns -- thank goodness!  Go back and read ID:35483127.  You should be able to use that answer.
0
 
LVL 4

Expert Comment

by:msk_apk
ID: 35483501
by just having only end_date and with out duration how will you find the start date of the contract. there is no means to find the current duration of the customer with out start date or with out duration.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35483519
I have both startdate and end date. Im sure i can proberly just use 18 month interval to see who is coming to their last 6 months ill give it a dabble and see what i can come up with/
0
 
LVL 4

Expert Comment

by:msk_apk
ID: 35483658
@Ray_Paseur: i have not seen your reply. just replied by seeing the question. if my answer is same as yours let the author decide what to do. dont ASSUME things.
0
 
LVL 6

Author Comment

by:NeoAshura
ID: 35483701
Ok sorted, I used Rays example in  ID:3548312 and this made alot of sense used this to create a query and it works fine. Thanks for the help points will be awarded accordingly.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to dynamically set the form action using jQuery.
Suggested Courses

621 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question