How to validate a user's date inputs when the validation criteria is stored in an SQL database

Question

i has the next table:

| ID | Date-Start |Date-End|
| 01 | 2014-04-10 |2014-04-12|
| 02 | 2014-04-13 |2014-04-15|
| 03 | 2014-04-16 |2014-04-18|
| 04 | 2014-04-19 |2014-04-22|

The user select 2 dates and i need to validate this 2 dates not cross with the ones in table. For example user enter 2014-04-20 to 2014-04-21, this is invalid because in record 04 this range are occupied from 2014-04-19 to 2014-04-22. Ty for help. Excuse my bad english.

This might be helpful: http://stackoverflow.com/questions/2545947/mysql-range-date-overlap-check — David Wilkins, Apr 07 '14 at 15:09

score 0 · Answer 1 · answered Apr 07 '14 at 15:12

0

SELECT ID FROM table WHERE ((Date-Start >= '$first-date' AND Date-Start <= '$second-date')
 OR (Date-End >= '$first-date' AND Date-End <= '$second-date'))

If it returns a result then the given period should be invalid

answered Apr 07 '14 at 15:12

gbestard

1,177
13
29

Hi gbestard, ty for your response, but the select dont work for me because if i choose this dates: 2014-04-20 to 2014-04-21, the select dont take this range like occupied and dont return nothing, and this should be return the last record (| 04 | 2014-04-19 |2014-04-22|), ty – user3507250 Apr 08 '14 at 11:57
If it doesn't return anything then the given period is ok – gbestard Apr 08 '14 at 12:28

Chrisky · Answer 2 · 2014-04-08T16:42:25.963

0

So you can do

(r1.dateEnd > r2.dateStart) and (r2.dateEnd > r1.dateStart)

to see if the rows overlap. e.g. to find overlapping rows

select * from DateTable r1, DateTable r2 
where (r1.dateEnd > r2.dateStart) and (r2.dateEnd > r1.dateStart)

or to find the overlapping dates with a query:

select * from DateTable r1
where ( r1.dateEnd >= {start_date} ) and ( {end_date} >= r1.dateStart )

edited Apr 08 '14 at 16:42

answered Apr 07 '14 at 15:13

Chrisky

567
3
9

Hi Chrisky, excuse me but i are a little new in mysql, i dont understand how this work, what is r1 and r2?, and where i put the dates provided for user, ty – user3507250 Apr 08 '14 at 12:00
Sorry, I could have been clearer. The example above spots overlapping entries in the one DateTabe - it's aliased to r1 and r2. I've edited the answer. The crucial thing is to have a comparison like the one I've given - this covers all circumstances of overlapping, e.g. when one span completely contains another span. If you use the other types of comparison outlined here, you'll not catch that case. – Chrisky Apr 08 '14 at 16:45

score 0 · Answer 3 · answered Apr 07 '14 at 15:13

0

Using pseudo code:

SELECT COUNT(*) FROM `TABLE` WHERE `Date-Start` BETWEEN <start-date> AND <end-date> OR `Date-End` BETWEEN <start-date> AND <end-date>

Then you can check if count > 0. If it is, you know the dates are occupied.

answered Apr 07 '14 at 15:13

Samsquanch

8,866
12
50
89

Hi Samsquanch, ty for your response, but the select dont work for me because if i choose this dates: 2014-04-20 to 2014-04-21, the select dont take this range like occupied and return 0, and this should be return the last record (| 04 | 2014-04-19 |2014-04-22|), ty – user3507250 Apr 08 '14 at 11:54

score -2 · Answer 4 · edited Sep 17 '19 at 17:32

-2

SELECT * FROM table WHERE
 (( $fIni BETWEEN fechaIni AND fechaFin)
 OR ($fFin BETWEEN fechaIni AND fechaFin))

edited Sep 17 '19 at 17:32

Dharman

30,962
25
85
135

answered Sep 17 '19 at 17:28

vbalvarez15

1

2

add certain explanation as well to your answer – Shubham Sep 17 '19 at 18:02

How to validate a user's date inputs when the validation criteria is stored in an SQL database

4 Answers4