Problem with datevalue(

I have a procedure that scans my database to select the records of clients who should receive an annual reminder postcard. This procedure has been working for over a year. I enter the date to be selected as mm/1/yy, using the current year. The procedure uses monthmath( to subtract 12 months from the current year, and then uses monthlength( to determine the length of the month. This information then is used in datevalue( so that the correct dates for the 1st and last days of the month can be used to Select the appropriate records.

Today, I ran this procedure and found that datevalue( will cause an error when the months of August, October, or December are used. It does not accept the number 31 as a “day”. It does work properly when any of the other months with 31 days are entered, and it works with any of those months if “day” is set to 30.

This is the error message: datevalue( function: Illegal date, day must be from 1 to 30).

Since this procedure was working before upgrading to the Panorama X Pro 10.2.0.b24 (3860), I am curious to know if that has produced this change. I tried running this procedure in Panorama 10.1.2, but that Panorama closes before I can do anything with it. In any case, I will probably change the procedure to work around this problem, but any insight would be welcome

Below is a short procedure which produces the same error. (Sorry, but I can never remember how to properly add code to a post.)


let monthAndYearEntered = ""
gettextsheet "Enter: mm/1/yy", monthAndYearEntered 
let firstOfMonthLastYear = monthmath(date(monthAndYearEntered), -12)
message datestr(firstOfMonthLastYear)
message monthlength(firstOfMonthLastYear)

message datestr(datevalue(yearvalue(firstOfMonthLastYear), monthvalue(firstOfMonthLastYear), monthlength(firstOfMonthLastYear)))

Confirmed. The error occurs in 10.2 (build 3860)

but not in 10.1.2

Thank you Dave. Now I can stop fidgeting with it as it is, and come at it from another angle.

Since firstOfMonthLastYear contains a date, you can get the date for the end of the month by jumping to the first of the next month, and subtracting 1 day.

datestr(monthmath(firstOfMonthLastYear, 1) - 1)

That is very simple and slick. I like it.
Thanks again, Dave.

In fixing one bug, a new bug was created. In Panorama X 10.1, the datevalue( function would accept any day up to 31 for any month, even February. Panorama X 10.2 added better error checking, but didn’t do so correctly for the months August-December. Not only does it reject August 31, it allows September 31 with no error (of course the wrong value is calculated). It’s also wrong for October, November and December. I’ve just now added this to the bug list.

Dave’s solution is more slick, but another solution would be:


This takes advantage of the fact that the date( function will understand dates in the form YYYY-MM-DD no matter what your local date setting is (and that the date( function doesn’t have the same bug that the datevalue( function has).

Hi Jim. Thank you for the update. I know that I am only using a very small part of an enormous program, but it is nice to know how and when things fit together. I will keep both your and Dave’s alternatives in mind as I reform my procedure.