check if date on FORM is between two values from table (multiple records)

Scriptcase 8 Discussion
#1

Hi guys,

I have a table with 2 date fields in it. a field date1 and date2. I have multiple rows with to dates on each row.

eg:
christmas - 25/12/2016 - 26/12/2016 - everyYearYES ------ 2 days - samer period every year
NewYear - 01/01/2017 - 02/01/2017 - everyYearYES ------ same
Holiday - 02/07/2016 - 02/07/2016 - everyYearNo ------ 1day changes every year
…
I want to send the user a message (and stop adding) when the date he entered is BETWEEN one of the dates on every row. Some value return EVERY year some of them NOT

Some one has some code to do so?

#2

anyone any idea?

#3

hmmm break it down a bit better …
Do you want to check if a date is in a range ?
http://stackoverflow.com/questions/976669/how-to-check-if-a-date-is-in-a-given-range

#4

yes i do

but sometimes the date has a yes value aswell which means that it returns every year without repeating it in the table. Like New Year, Christmas,… So when the table have the value for New Year from 01/01/2016 to 02/01/2016 it should be the same for 2017, 2018,…

#5

Seems you are working on a master and detail tables. On cases like this, i find it easier to write database queries and call them within onValidate event or as an Ajax onChange event than coding them on php

#6

It’s just a normal FORM to validate the date the user entered. When date is between dates in an other table I want to pop up a message and delete the entry. Simple in words but…

#7

Nice way to solve this with a query.

But in my table i have some fixed dates like Christmas set as holiday from December 25th till December 26th for EVERY year. If I should run the query it would result as 0.

In my table i have 2 date fields and 1 yes/no field. So Newyear is set as LeaveFrom= 31/12/14; LeaveTo=02/01/15 and Yearly=YES. The reason why the year is 2014 and 2015 is because they were entered in the table in 2014.

So i think I have to find a way to change the YEAR of the LeaveFrom and LeaveTo to present YEAR 2016 (LeavrFrom) and 2017 (LeaveTo).

Thinking… :slight_smile:

#8

I’m not so sure but maybe the built in function in MySQL might help you

SELECT YEAR(LeaveFrom)
SELECT YEAR(LeaveTo)
SELECT YEAR(NOW())

On my payroll system, since December 25 is a Holiday that repeats every year, the simplest solution I could think of is to ensure that the table I needed this date have the following entries:

2016-12-25 Christmas
2017-12-25 Christmas
2018-12-25 Christmas

I run an event script within MySQL that produces the date annually. Then the script is inside an event or some stored procedures that is being called at least once a year. I make sure I have enough forecast for at least 2 years. If not, then script will autogenerate those dates that are marked EVERY.