Page 1 of 1

datediff function

Posted: June 11th, 2012, 5:14 pm
by lls
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

Re: datediff function

Posted: June 12th, 2012, 10:42 am
by Gregory Martin
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

Posted: June 13th, 2012, 7:21 am
by lls
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))

Re: datediff function

Posted: June 6th, 2013, 1:37 pm
by Maniedru
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. :? :?

Re: datediff function

Posted: June 6th, 2013, 6:21 pm
by Gregory Martin
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;

Re: datediff function

Posted: June 7th, 2013, 10:09 am
by Maniedru
Thank you so much gregory :D :D :D :D

Re: datediff function

Posted: March 15th, 2024, 10:17 am
by Rchari
I want to calculate age of a person using entered date of birth (day, month, year) and system date

Re: datediff function

Posted: March 15th, 2024, 11:35 am
by justinlakier
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.
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,
Justin

Re: datediff function

Posted: March 18th, 2024, 1:40 am
by Rchari
Thanks it helped