Ggtcf

Were any external disk drives stacked vertically?

90's TV series where a boy goes to another dimension through portal near power lines

Why are electrically insulating heatsinks so rare? Is it just cost?

If human space travel is limited by the G force vulnerability, is there a way to counter G forces?

Where does SFDX store details about scratch orgs?

Watching something be written to a file live with tail

Is it legal for company to use my work email to pretend I still work there?

Combinations of multiple lists

Why was the shrinking from 8″ made only to 5.25″ and not smaller (4″ or less)?

Could gravitational lensing be used to protect a spaceship from a laser?

What is a clear way to write a bar that has an extra beat?

What killed these X2 caps?

Is it inappropriate for a student to attend their mentor's dissertation defense?

Do I have a twin with permutated remainders?

I would say: "You are another teacher", but she is a woman and I am a man

Doing something right before you need it - expression for this?

Intersection of two sorted vectors in C++

Why is the 'in' operator throwing an error with a string literal instead of logging false?

Is it possible to run Internet Explorer on OS X El Capitan?

What's the point of deactivating Num Lock on login screens?

Why does Arabsat 6A need a Falcon Heavy to launch

What to put in ESTA if staying in US for a few days before going on to Canada

What reasons are there for a Capitalist to oppose a 100% inheritance tax?

How do conventional missiles fly?

MySQL query - check between two dates without needing to retrieve rows

How do I query between two dates using MySQL?How do you write a conditional in a MySQL select statement?MySQL - UPDATE query based on SELECT QueryMYSQL - Retrieve Timestamps between datesmysql between two dates indexHow to check if a date is between date1 and date2 using mysql?MYSQL query, conditional join if in between two datesFilter between two dates MYSQLMySql: Query select two dates having one month difference betweenHow to select rows if given date is between two dates?MySQL query table to get data between start and end dateHow to query a date between two other dates in another table

.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;

-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.

mysql select

share|improve this question

asked Mar 8 at 23:49

sadsongcosadsongco

84

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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 at 8:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And finally figuring out how to properly phrase the question I figured out how to find the answer, here: link Thanks for your help!
  
  – sadsongco
  Mar 10 at 8:41
  

  
  
  
  

add a comment | 

-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.

mysql select

share|improve this question

asked Mar 8 at 23:49

sadsongcosadsongco

84

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 8 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 at 8:01
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  And finally figuring out how to properly phrase the question I figured out how to find the answer, here: link Thanks for your help!
  
  – sadsongco
  Mar 10 at 8:41
  

  
  
  
  

add a comment | 

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.

mysql select

share|improve this question

asked Mar 8 at 23:49

sadsongcosadsongco

84

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

share|improve this question

asked Mar 8 at 23:49

sadsongcosadsongco

84

share|improve this question

asked Mar 8 at 23:49

sadsongcosadsongco

84

asked Mar 8 at 23:49

sadsongcosadsongco

84

asked Mar 8 at 23:49

sadsongcosadsongco

84

3 Answers
3

active

oldest

votes

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,

share|improve this answer

edited Mar 8 at 23:57

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You should assign an alias to the expression, to make it easier to retrieve the value.
  
  – Barmar
  Mar 8 at 23:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Good point, will update
  
  – Miroslav Glamuzina
  Mar 8 at 23:56
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9: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 9 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 9 at 18:30
  

  
  
  
  

 | 
show 3 more comments

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?

share|improve this answer

answered Mar 8 at 23:57

OnurguleOnurgule

5817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9:56
  

  
  
  
  

add a comment | 

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.

share|improve this answer

answered Mar 11 at 12:22

sadsongcosadsongco

84

add a comment | 

Your Answer

StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");

StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);

else
createEditor();

);

function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);



);

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);

Sign up using Google

Sign up using Facebook

Sign up using Email and Password

Post as a guest

Name

Email

Required, but never shown

StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55072554%2fmysql-query-check-between-two-dates-without-needing-to-retrieve-rows%23new-answer', 'question_page');

);

Post as a guest

Name

Email

Required, but never shown

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,

share|improve this answer

edited Mar 8 at 23:57

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You should assign an alias to the expression, to make it easier to retrieve the value.
  
  – Barmar
  Mar 8 at 23:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Good point, will update
  
  – Miroslav Glamuzina
  Mar 8 at 23:56
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9: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 9 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 9 at 18:30
  

  
  
  
  

 | 
show 3 more comments

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,

share|improve this answer

edited Mar 8 at 23:57

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  You should assign an alias to the expression, to make it easier to retrieve the value.
  
  – Barmar
  Mar 8 at 23:55
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  Good point, will update
  
  – Miroslav Glamuzina
  Mar 8 at 23:56
  
  

  
  
  
  


• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9: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 9 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 9 at 18:30
  

  
  
  
  

 | 
show 3 more comments

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,

share|improve this answer

edited Mar 8 at 23:57

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

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

share|improve this answer

edited Mar 8 at 23:57

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

answered Mar 8 at 23:54

Miroslav GlamuzinaMiroslav Glamuzina

2,09311023

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?

share|improve this answer

answered Mar 8 at 23:57

OnurguleOnurgule

5817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9:56
  

  
  
  
  

add a comment | 

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?

share|improve this answer

answered Mar 8 at 23:57

OnurguleOnurgule

5817

• 
  
  
  

  
  

  
  
  
  
  
  

  
  
  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 9 at 9:56
  

  
  
  
  

add a comment | 

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?

share|improve this answer

answered Mar 8 at 23:57

OnurguleOnurgule

5817

(beginning_date_from_table) and (end_date_from_table)

(end_date_from_table) and (beginning_date_from_table)

share|improve this answer

answered Mar 8 at 23:57

OnurguleOnurgule

5817

answered Mar 8 at 23:57

OnurguleOnurgule

5817

answered Mar 8 at 23:57

OnurguleOnurgule

5817

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.

share|improve this answer

answered Mar 11 at 12:22

sadsongcosadsongco

84

add a comment | 

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.

share|improve this answer

answered Mar 11 at 12:22

sadsongcosadsongco

84

add a comment | 

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.

share|improve this answer

answered Mar 11 at 12:22

sadsongcosadsongco

84

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

share|improve this answer

answered Mar 11 at 12:22

sadsongcosadsongco

84

answered Mar 11 at 12:22

sadsongcosadsongco

84

answered Mar 11 at 12:22

sadsongcosadsongco

84