-1

I have a table that includes dates, I'm trying to check if a date I have falls between the dates in the table. My query is working, but it doesn't return anything. This seems like it should be very simple, but I can't wrap my head around it.

SQL query looks like this:

SELECT id FROM table
WHERE
(this_date) between (beginning_date_from_table) and (end_date_from_table)

The dates are generated dynamically in my script so I can ascertain if what I'm passing into it falls between the beginning and end dates in my table. I don't need any specific data from the table, just a boolean telling me whether the date is between the beginning and end dates or not.

sadsongco
  • 21
  • 6
  • Your query as it is should be fine, just check to see if you get any rows returned or not. If you did, the date is in the range, if you didn't then it isn't. – Nick Mar 08 '19 at 23:55
  • Thank you both for replying. I did read the above question and many others here and elsewhere before posting. It doesn't address my exact question which is wanting to return the result of a test on retrieved data, rather than retrieve any data. There's a longer explanation in the comments below Miroslav's post below. – sadsongco Mar 10 '19 at 08:01
  • And finally figuring out how to properly phrase the question I figured out how to find the answer, here: [link](https://stackoverflow.com/questions/1647961/how-do-you-write-a-conditional-in-a-mysql-select-statement) Thanks for your help! – sadsongco Mar 10 '19 at 08:41

3 Answers3

0

You are looking for EXISTS:

SELECT 
  EXISTS(
    SELECT id FROM table 
  WHERE
    (this_date) between (beginning_date_from_table) and (end_date_from_table)
  ) AS hasValue

Hope this helps,

Community
  • 1
  • 1
Miroslav Glamuzina
  • 4,472
  • 2
  • 19
  • 33
  • 1
    You should assign an alias to the expression, to make it easier to retrieve the value. – Barmar Mar 08 '19 at 23:55
  • Thank you Miroslav. Unfortunately EXISTS returns a value of 0 because I'm not asking MySQL to return any rows, but only check whether a date exists between two other dates in a table. I can get the same result in PHP by retrieving the beginning and end dates from the tables and checking if this_date is between them, I was hoping there was a more elegant way to do it in pure SQL. I'm aware my syntax is wrong because I'm asking for something that isn't there, but I can't figure out how to return some kind of boolean based on the conditional. – sadsongco Mar 09 '19 at 09:58
  • Hi there, the results of `EXISTS()` are Boolean, `0` if false, `1` if true, regardless on how many rows are present. Is this not what you are looking for? – Miroslav Glamuzina Mar 09 '19 at 17:23
  • Thanks for taking the time to reply again. I think I'm asking MySQL to do something it isn't really designed for. Replacing variables in my original post, I want to know if '2007-02-04 between 2007-01-24 and 2007-02-16'. The first date is passed in as a variable, the second and third are retrieved from tables. EXISTS doesn't unfortunately solve it. Per the doc, 'If a subquery returns any rows at all, EXISTS subquery is TRUE, and NOT EXISTS subquery is FALSE.' The query never returns any rows at all, so EXISTS is always FALSE. Thanks for trying. I have an inelegant solution that I will use :) – sadsongco Mar 09 '19 at 18:30
  • Out of curiosity, what is your solution? I feel like I am misunderstanding the question.. – Miroslav Glamuzina Mar 09 '19 at 22:39
  • My fault entirely, I feel I'm not phrasing the question well. My solution is - SQL query the table for beginning_date - SQL query the table for end_date - PHP strtotime this_date, beginning_date and end_date so they are all UNIX timestamps - PHP if this_date > beginning_date and this_date < end_date then return true which works fine. I just felt like there must be an elegant way to test the variable in SQL while selecting the beginning_date and end_date Thank you for taking the time to help. – sadsongco Mar 10 '19 at 07:57
  • And finally figuring out how to properly phrase the question I figured out how to find the answer, here: [https://stackoverflow.com/questions/1647961/how-do-you-write-a-conditional-in-a-mysql-select-statement](https://stackoverflow.com/questions/1647961/how-do-you-write-a-conditional-in-a-mysql-select-statement). So my solution is `SELECT (CASE WHEN this_date BETWEEN beginning_date AND end_date THEN 1 ELSE 0 END) AS date_result`, and it works perfectly. Thanks for your help! – sadsongco Mar 10 '19 at 08:46
0

Check your MySQL server's date format and your generated date format.

MySQL date format is like that: '2019-01-30 18:19:52'

Also you can try change 

(beginning_date_from_table) and (end_date_from_table)

to 

(end_date_from_table) and (beginning_date_from_table)

Check: How do I query between two dates using MySQL?

Onurgule
  • 707
  • 8
  • 21
  • Thank you for the reply. Unfortunately it's not the dates that are a problem, it's the fact that I'm not asking MySQL to return any rows, but only check whether a date exists between two other dates in a table. I can get the same result in PHP by retrieving the beginning and end dates from the tables and checking if this_date is between them, I was hoping there was a more elegant way to do it in pure SQL. – sadsongco Mar 09 '19 at 09:56
0

I phrased the question slightly wrong, in that I was trying to return the result of a conditional. Once I realised how to ask google the right thing, I quickly came up with this solution:

SELECT (CASE WHEN this_date BETWEEN beginning_date AND end_date THEN 1 ELSE 0 END) AS date_result

Thanks to the other people who answered, your input put my thinking on the right track.

sadsongco
  • 21
  • 6