Jump to content

Selecting Date + 3 Days


MoFish

Recommended Posts

Hi,

 

Another date query which I'm struggling with. Would anyone be able to help me at all?

 

I have the following table.

ID, DDATE

1, 09/02/2017 (date/time)
2, 15/02/2017 (date/time)
3, 20/02/2017 (date/time)

I'm trying to only select the values from the DB where the CURRENTDATE is equal to the DDATE value + 3 days afterwards.

 

For example: Once the CURRENDATE is 09/02/2017 it should return row 1 until the CURRENTDATE hits the 12/02/2017 as that's + 3 days.

 

It should not return row 2 or 3 until the actual date (15/02/2017) or (20/02/2017) is the CURRENTDATE

 

I tried this myself a few times and failed - the results are not correct. I used UTC_DATE() in my queries as my server is based in America and i'm in UK.

 

Any help much appreciated.

 

Thanks,

 

MoFish

SELECT * FROM `TABLE` WHERE `DDATE` = UTC_DATE() AND `DDATE` BETWEEN (DATE((`DDATE`) + INTERVAL 3 DAY) AND UTC_DATE())
SELECT * FROM `TABLE` WHERE (`DDATE`) BETWEEN UTC_DATE() AND (`DDATE`) + INTERVAL 3 DAY
SELECT * FROM TABLE WHERE UTC_DATE() = DATE(`DDATE`) + 3 DAY INTERVAL
Link to comment
Share on other sites

If you think about it that way, it's straightforward: find all the rows that aren't too far into the future, sort them by the date in descending order (latest first), and pick the first one.

SELECT whatever FROM table WHERE date is no more than 3 days in the future ORDER BY date DESC LIMIT 1
Link to comment
Share on other sites

Hi Requinix, Thanks for your reply.

 

I may have not communicated correctly what I am trying to achieve - as there could often be more than one result.

 

Let's say I have the following data in my TABLE and today's date is the 09/02/2017

ID, DDATE

1, 09/02/2017 (date/time)
2, 15/02/2017 (date/time)
3, 20/02/2017 (date/time)
4, 07/02/2017 (date/time)
5, 09/02/2017 (date/time)
6, 06/02/2017 (date/time)

I would expect this to return the following results:

ID, DDATE

1, 09/02/2017 (today)
4, 07/02/2017 (tomorrow - inside 3 day range)
5, 09/02/2017 (today)
6, 06/02/2017 (3rd day ago - inside 3 day range)

I thought the SQL query would have been something like the following - however this does not work nor is it fully valid.
 

// SELECT * FROM TABLE WHERE DATE IS BETWEEN TODAYS DATE AND TODAYS DATE + 3 DAYS

SELECT * FROM TABLE WHERE (`DDATE` BETWEEN UTC_DATE() AND UTC_DATE() + 3 DAY INTERVAL);

Regards,

 

MoFish

Edited by MoFish
Link to comment
Share on other sites

Hi Requinex,

 

Yes, I looked at your query - however noticed you had put a limit on the end of the query - which made me think we were talking about differing things. I could potentially expect more than one result as per my example results above. I have tried multiple times to do the 'middle bit' but am struggling a little. I thought my query above was close.

Edited by MoFish
Link to comment
Share on other sites

Jacques1 & requinex - Sorry about this.

 

I'm still not getting the results I require. The following is still not returning the correct data - even without the -.

 

I have attempted so many queries myself and none appear to be correct.

SELECT * FROM TABLE WHERE DATE(`DDATE`) = DATE_ADD(UTC_DATE(), INTERVAL 3 DAY)
Link to comment
Share on other sites

Hi requinex,

 

Yes, I want all of the results. I have tried your query - however this is not working quite as expected. Maybe this will help explain what I'm trying to do:

if TODAY is in the 3 DAY RANGE of DDATE value - then display results
Edited by MoFish
Link to comment
Share on other sites

Hi Barand,

 

Thanks for this. This was not producing the results as I was expecting - however I have modified the query and this seems to be working now.

SELECT * FROM TABLE WHERE `DDATE` BETWEEN (UTC_DATE() - INTERVAL 2 DAY) AND UTC_DATE()

Thanks everyone for taking the time to respond.

 

MoFish

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.