Start date between start date and end date

Question

I want to check if a date exists between the start date and the end date.

I added a where clause

where datepaid between '2010-04-01' AND '2010-04-15' 

but the problem is that it excludes "2010-04-15".

It should include the end date and how can I do this?

Please help me with this.

Hi,

Pankai

Answer 1

Indicate the time parts explicitly:

 WHERE datepaid BETWEEN '2010-04-01 00:00:00' AND '2010-04-15 23:59:59' 

Answer 2

Perhaps your dates include intraday times. Try instead:

 where '2010-04-01' <= datepaid AND datepaid < '2010-04-16' 

Answer 3

 WHERE datepaid >= '2010-04-01' AND datepaid < '2010-04-15' + INTERVAL 1 DAY 

Answer 4

to try

 where datepaid between '2010-04-01' AND '2010-04-15 23:59:59' 

Answer 5

This is probably due to dates having a time:

 select '2010-04-15' between '2010-04-01' AND '2010-04-15', -- 1 '2010-04-15 12:00:00' between '2010-04-01' AND '2010-04-15', -- 0 '2010-04-15 12:00:00' between '2010-04-01 00:00:00' AND '2010-04-15 23:59:59' -- 1 

Either add time to the range delimiters, or trim the values ​​before comparing:

 select '2010-04-15 12:00:00' between '2010-04-01 00:00:00' AND '2010-04-15 23:59:59' -- 1 cast('2010-04-15 12:00:00' as date) between '2010-04-01' AND '2010-04-15' -- 1 

I have not tested it, but I suppose performance is better without casting.

Answer 6

where CONVERT (VARCHAR (10), datepaid, 23) between '2010-04-01' AND '2010-04-15'

Answer 7

add this section:

 OR datepaid = '2010-04-15'