I would like to prevent keyers to make mistakes in entering dates with the datediff function which calculate the difference between date1 and date2 (closing date2 should not be earlier than starting date1).
In my form, I have used dd, mm, yyyy as 3 subitems (say: 'day1', 'month1', 'year1' for start date and 'day2', 'month2', 'year2' for end date), so I don't have a single 8 digit date1 field and date2 field.
I would like to display a warning message in the case where, by mistake, the date2 would be chronologically before the date1.
Could you please users help me to find a way to resolve this problem?
Thank you
datediff function
-
- Posts: 1796
- Joined: December 5th, 2011, 11:27 pm
- Location: Washington, DC
Re: datediff function
You can create the input to the datediff function by combining your three variables, something like this:
datediff(20120612,YEAR * 10000 + MONTH * 100 + DAY);
Re: datediff function
Thank you so much.
For anyone interested, this is what I did. I don't know if this is the best solution but it works.
(on the year subitem)
if datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2) < 0 then
errmsg(2, DAY1, MONTH1, YEAR1, DAY2, MONTH2, YEAR2,
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"md"),
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"ym"),
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"y")),
select("Change day", DAY2,"Change month", MONTH2,"Change year", YEAR2);Endif;
And the error message being:
2 The difference between date1 (%d/%d/%d) and date2 (%d/%d/%d) cannot be negative (%d day(s), %d month(s), and %d year(s))
For anyone interested, this is what I did. I don't know if this is the best solution but it works.
(on the year subitem)
if datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2) < 0 then
errmsg(2, DAY1, MONTH1, YEAR1, DAY2, MONTH2, YEAR2,
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"md"),
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"ym"),
datediff(YEAR1 * 10000 + MONTH1 * 100 + DAY1, YEAR2 * 10000 + MONTH2 * 100 + DAY2,"y")),
select("Change day", DAY2,"Change month", MONTH2,"Change year", YEAR2);Endif;
And the error message being:
2 The difference between date1 (%d/%d/%d) and date2 (%d/%d/%d) cannot be negative (%d day(s), %d month(s), and %d year(s))
Re: datediff function
i am in a similar problem. I want to compare the month and year of date entered to the month and year current system date, get the difference and compare with an age entered earlier. Please help.
![Confused :?](./images/smilies/icon_e_confused.gif)
![Confused :?](./images/smilies/icon_e_confused.gif)
![Confused :?](./images/smilies/icon_e_confused.gif)
-
- Posts: 1796
- Joined: December 5th, 2011, 11:27 pm
- Location: Washington, DC
Re: datediff function
If you only have a month and a year (and not the date), you might look at the cmcode function. For example, to get someone's age based on their date of birth, you could write:
numeric calculatedAge = int( ( cmcode(sysdate("MM"),sysdate("YYYY")) - cmcode(BIRTH_MONTH,BIRTH_YEAR) ) / 12 );
if AGE <> calculatedAge then
// errmsg
endif;
if AGE <> calculatedAge then
// errmsg
endif;
Re: datediff function
Thank you so much gregory
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
![Very Happy :D](./images/smilies/icon_e_biggrin.gif)
Re: datediff function
I want to calculate age of a person using entered date of birth (day, month, year) and system date
-
- Posts: 152
- Joined: November 21st, 2022, 4:41 pm
Re: datediff function
This is an 11 year old thread. Please try not to add to "dead" threads that are more than a year old. Furthermore, the existing answers in this thread should already demonstrate the sysdate() and datediff() functions you need. You can look to the documentation for more on these functions. Here is an example using these functions to find the age in years given birth year as a 4 digit item and month and day as 2 digit items, which we can add as Greg demonstrated to get a YYYYMMDD format.
Justin
numeric birthDate = BIRTH_YEAR * 10000 + BIRTH_MONTH * 100 + BIRTH_DAY;
numeric currentDate = sysdate("YYYYMMDD");
errmsg("Age is %v years.", datediff(birthDate, currentDate, "y"));
Hope this helps,numeric currentDate = sysdate("YYYYMMDD");
errmsg("Age is %v years.", datediff(birthDate, currentDate, "y"));
Justin