using BETWEEN operator twice in one SQL statement

Question

This is what I have tried, but did not work.

SELECT COUNT(*) AS month_count
FROM `wp_postmeta`
WHERE
     `meta_key`='from_dt' AND
      (`meta_value` BETWEEN '$st' AND '$end') AND
      (`meta_value` BETWEEN '$st1' AND '$end1');

I'm trying to count the number of months which come between epoch time of $st and $end of this year & between $st1 and $end1 of next year. Say I have 2 dates (feb 2013 and feb 2014)in meta_value fields, I want the query to return 2(as there are 2 February's).

How to use 2 between operators in one sql statement?

The meta_value cannot be two different values at the same time, do you mean OR? — Taryn
– Taryn, Commented May 2, 2013 at 14:03
Actually i'm trying to count the number of months which come between epoch time of $st and $end of this year & $st1 and $end1 of next year. — Nagendra Rao
– Nagendra Rao, Commented May 2, 2013 at 14:07

sgeddes · Accepted Answer · 2013-05-02 14:05:49Z

2

Nothing technically wrong with your query, but depending on your desired results, you probably want to be using OR -- just make sure your parentheses are in the correct place:

SELECT COUNT(*) AS month_count
FROM `wp_postmeta`
WHERE
     `meta_key`='from_dt' AND
      ((`meta_value` BETWEEN '$st' AND '$end') OR
      (`meta_value` BETWEEN '$st1' AND '$end1'))

Your current query is requiring the meta_value be between both sets of numbers which may be your problem.

answered May 2, 2013 at 14:05

sgeddes

62.9k7 gold badges67 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

John Woo · Accepted Answer · 2013-05-02 14:05:33Z

0

do you mean this?

SELECT COUNT(*) AS month_count
FROM `wp_postmeta`
WHERE
      (`meta_key`='from_dt' AND `meta_value` BETWEEN '$st' AND '$end') OR
      (`meta_key`='from_dt' AND `meta_value` BETWEEN '$st1' AND '$end1')

answered May 2, 2013 at 14:05

John Woo

265k70 gold badges508 silver badges500 bronze badges

Comments

Gordon Linoff · Accepted Answer · 2013-05-02 14:05:50Z

0

Perhaps you are looking for two different rows that are in these ranges. For that, use a having clause:

SELECT COUNT(*) AS month_count
FROM `wp_postmeta`
WHERE `meta_key`='from_dt'
having sum(case when `meta_value` BETWEEN '$st' AND '$end' then 1 else 0 end) > 0 AND
       sum(case when `meta_value` BETWEEN '$st1' AND '$end1' then 1 else 0 end) > 0);

answered May 2, 2013 at 14:05

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

Comments

Vishwanath Dalvi · Accepted Answer · 2013-05-02 14:06:10Z

0

SELECT COUNT(*) AS month_count 
FROM `wp_postmeta` 
WHERE `meta_key`='from_dt' AND
(`meta_value` BETWEEN '$st' AND '$end') OR (`meta_value` BETWEEN '$st1' AND '$end1');

answered May 2, 2013 at 14:06

Vishwanath Dalvi

36.9k41 gold badges128 silver badges156 bronze badges

Comments

Luixv · Accepted Answer · 2013-05-02 14:07:41Z

0

I think that you want an OR there instead of an AND. Think that in an AND bith conditions must be satisfied whereas in an OR one condition is enough.

answered May 2, 2013 at 14:07

Luixv

8,73021 gold badges87 silver badges122 bronze badges

Collectives™ on Stack Overflow

using BETWEEN operator twice in one SQL statement

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Related