SQL between date today and beyond

Question

I am making a Query for a project that I am working on. I have to make a query that shows me the dates from Today and the dates that will still come. At the moment my Query is this:

$query = "SELECT * FROM systeem 
RIGHT JOIN vestiging ON vestiging.id = systeem.vestigingID 
LEFT JOIN systeemMeldkamer ON systeemMeldkamer.systeemID = systeem.id 
LEFT JOIN meldkamers ON meldkamers.id = systeemMeldkamer.meldkamerID 
LEFT JOIN systeemContract ON systeemContract.systeemID = systeem.id 
LEFT JOIN onderhoudsLog ON onderhoudsLog.systeemContractID = systeemContract.id  
LEFT JOIN contracten ON contracten.ident = systeemContract.contractIDENT
WHERE onderhoudsLog.onderhoudsDatum  LIKE '2017%'  
ORDER BY onderhoudsLog.onderhoudsDatum ASC";

This Query will give me all dates that look like "2017%". Instead I want to get the date of today and every date that will come after the date of today:

today it is : 2018-03-05

The result that I want to get: 2018-03-05, 2018-03-06, 2018-03-07

Tomorrow It is: 2018-03-06 And then it has to search for all dates after 2018-03-06

I hope any one can help with my query! Don't mind the "LEFT JOINS", it is because I have to go through 6 tables so I can match the date for maintenance with the number if our customer.

use `WHERE onderhoudsLog.onderhoudsDatum >= NOW()` if you're on mysql — ariefbayu, Mar 05 '18 at 08:29
See this [Datetime equal or greater than today in MySQL](https://stackoverflow.com/questions/5182275/datetime-equal-or-greater-than-today-in-mysql) — Adlan Arif Zakaria, Mar 05 '18 at 08:30
@Mittal I am working with "phpmyadmin" sql, onderhoudsDatum is indeed the date field — Yannick Gijsbers, Mar 05 '18 at 08:31
Not related to your question, but you may want to start using aliases. Also, your where clause turns most of your left joins into inner joins. — HoneyBadger, Mar 05 '18 at 08:31
@HoneyBadger Yes I didn't use alliases it is indeed not related to this question, but still thanks for the tip! It is something I really have to work with.. — Yannick Gijsbers, Mar 05 '18 at 08:32
@ariefbayu It worked!! Can you answer my question, so I can close the question :) — Yannick Gijsbers, Mar 05 '18 at 08:33

score 3 · Accepted Answer · answered Mar 05 '18 at 08:31

For MySQL, you can use below query. Add where clause like: WHERE onderhoudsLog.onderhoudsDatum >= CURDATE()

Assuming that onderhoudsDatum field is datetime datatype

$query = "SELECT * FROM systeem 
RIGHT JOIN vestiging ON vestiging.id = systeem.vestigingID 
LEFT JOIN systeemMeldkamer ON systeemMeldkamer.systeemID = systeem.id 
LEFT JOIN meldkamers ON meldkamers.id = systeemMeldkamer.meldkamerID 
LEFT JOIN systeemContract ON systeemContract.systeemID = systeem.id 
LEFT JOIN onderhoudsLog ON onderhoudsLog.systeemContractID = systeemContract.id  
LEFT JOIN contracten ON contracten.ident = systeemContract.contractIDENT
WHERE onderhoudsLog.onderhoudsDatum >= CURDATE()  
ORDER BY onderhoudsLog.onderhoudsDatum ASC";

score 0 · Answer 2 · answered Mar 05 '18 at 08:32

You could try this code

$today = date('Y-m-d');

$query = "SELECT * FROM systeem 
  RIGHT JOIN vestiging ON vestiging.id = systeem.vestigingID 
  LEFT JOIN systeemMeldkamer ON systeemMeldkamer.systeemID = systeem.id 
  LEFT JOIN meldkamers ON meldkamers.id = systeemMeldkamer.meldkamerID 
  LEFT JOIN systeemContract ON systeemContract.systeemID = systeem.id 
  LEFT JOIN onderhoudsLog ON onderhoudsLog.systeemContractID = systeemContract.id  
  LEFT JOIN contracten ON contracten.ident = systeemContract.contractIDENT
  WHERE onderhoudsLog.onderhoudsDatum  => $today  
  ORDER BY onderhoudsLog.onderhoudsDatum ASC";

You get the date using PHP's date() function, format it as a MySql date string and then use that on the query:

WHERE onderhoudsLog.onderhoudsDatum  => $today

score 0 · Answer 3 · answered Mar 05 '18 at 08:34

0

is the onderhoudsLog.onderhoudsDatum date field then use '>=CURRENT_DATE' Instead 'like "2017%"' and order by onderhoudsLog.onderhoudsDatum

answered Mar 05 '18 at 08:34

Pradeep Kumar

1
1

SQL between date today and beyond

3 Answers3