The validation of dates can be tricky. Partly this is due to JavaScript and partly down to the flexibility that Sage CRM provides in allowing dates to be presented.

I have covered how default values dates can be controlled in the article: Populating Default Date Field Values

Need for Validation

Date fields may need to have validation rules added. For example, when setting up a marketing campaign you may want to ensure that the end date follows the start date.

We would then need to compare the value entered into one field with another.

The two date field values would be accessible via the Values() collection.

I have discussed the use of the Values() collection in a previous article.

If we assume the custom field is called "camp_startdate" then we can retrieve that field's value by using

Values("camp_startdate")

But.... this will return a string and not a date.

So if we wish to compare dates then the string has to be first converted to a date value.

Note: There is an example of date comparison given in the CommunicationSchedulingBox for mobile devices on the comm_todatetime field.

try {
var actionValue = values("comm_action").toLowerCase();
if (actionValue.indexOf("meeting") >= 0) {
enddate = new Date(values("comm_todatetime_YEAR"), values("comm_todatetime_MONTH"), values("comm_todatetime_DAY"), values("comm_todatetime_HOUR"), values("comm_todatetime_MINUTE"), 0);
startdate = new Date(values("comm_datetime_YEAR"), values("comm_datetime_MONTH"), values("comm_datetime_DAY"), values("comm_datetime_HOUR"), values("comm_datetime_MINUTE"), 0);
if (enddate < startdate) {
Valid = false;
ErrorStr = "End date must be after start date";
}
}
} catch (e) {
Valid = false;
ErrorStr = "Not valid date" + e;
}
;

A date object in JScript can be created in a few ways.

dateObj = new Date()
dateObj = new Date(dateVal)
dateObj = new Date(year, month, date[, hours[, minutes[, seconds[,ms]]]])

It is the second method that is of most interest here.

The dateVal variable that can be passed to the constructor can be either a numeric value or a string.

If a numeric value, dateVal represents the number of milliseconds in Universal Coordinated Time between the specified date and midnight January 1, 1970.

If a string, dateVal is parsed according to the rules in the parse method. The dateVal argument can also be a VT_DATE value as returned from some ActiveX objects.

The strings that are going to be passed in Sage CRM via the Values() will be in short date format.

But the parsing of Dates can only use either a "/" or "-" date separator, but must follow the month/day/year format, for example, "7/20/96".

A user in Sage CRM can set their own date display preferences. Which can be one of 8 options.

Sage CRM date formats can be

d.m.yyyy
dd.mm.yyyy
dd/mm/yyyy
m.d.yyyy
mm.dd.yyyy
mm/dd/yyyy
yyyy.mm.dd
yyyy/mm/dd

We, therefore, have to parse the string from Values("camp_startdate") with reference to the users' date format preference before using it to create a date object.

You can access a user's date preferences by using

CurrentUser.user_prf

This has been discussed here.

A general discussion of the CurrentUser object can be found here.

Another way of grabbing the user's date preferences is to use

CRM.UserOption("NSet_UserDateFormat").toLowerCase();

But this is not documented and has historically had some issues. I prefer to use CurrentUser.user_prf

The example below uses regular expressions to check the date format. Other articles that discuss regular expressions in rules can be found here.

An example of checking a date field is not in the future:

var d, m, y;
//var userDateFormat = CRM.UserOption("NSet_UserDateFormat").toLowerCase();
var userDateFormat = CurrentUser.user_prf;
if (userDateFormat == "")
{
userDateFormat = CRM.SystemOption("DefaultUserDateFormat").toLowerCase();
}
if (userDateFormat.match(/d?d[/.]m?m[/.]yyyy/))
{
d = 1;
m = 2;
y = 3;
}
else if (userDateFormat.match(/m?m[/.]d?d[/.]yyyy/))
{
d = 2;
m = 1;
y = 3;
}
else if (userDateFormat.match(/yyyy[/.]m?m[/.]d?d/))
{
d = 3;
m = 2;
y = 1;
}
var dt = Values("camp_startdate").match(/(\d+)[/.](\d+)[/.](\d+)/);
if (new Date(dt[y], dt[m] - 1, dt[d]) > new Date())
{
ErrorStr = "Date entered is in the future";
Valid = false;
}

This should work against all the user date formats.

This technique can be applied to Table Level scripts and Entity Level scripts.