Finding date between start date and end date

Question

Hi,

I want to check whether a date is between start date and end date.

i have 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 end date also how can i do this?

Please help me on this

Regards,

pankaj

Answer 1

Perhaps your dates include intraday times. Try this instead:

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

Answer 2

try

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

Answer 3

add this clause:

OR datepaid = '2010-04-15'

Answer 4

Specify the time parts explicitly:

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

Answer 5

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

Answer 6

It's probably due to dates having 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 range delimiters or truncate values before comparison:

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 haven't checked but I suppose performance is better without casting.

Answer 7

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