This article has come about because of a question from a partner working on implementing Sage CRM where the customer would like dates shown according to the Hijri (Islamic) calendar.

Please note that this posting can't be regarded as an authoritative article on the inclusion of Islamic dates within Sage CRM. But they should give you some ideas.

But first, let us start with what we do know.

Sage CRM is dependent on the Gregorian calendar system which numbers its years since the traditionally reckoned year of the conception or birth of Jesus. This is the modern western dating system e.g.

Thirty days hath September,
April, June, and November;
Of twenty-eight, there is but one,
And all the rest have thirty-one

The problem is that Hijri dates are outside the range of Gregorian dates that both Sage CRM and the underlying database use.

For example, SQL Server can only store dates from 1753.

I did some testing with SQL Server 2005

update opportunity
set oppo_targetclose = '1753-12-04'
where oppo_opportunityid = 220;

This works, but...

update opportunity
set oppo_targetclose = '1752-12-04'
where oppo_opportunityid = 220;

Creates the SQL Error

"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."

Sage CRM throws in the restriction of not wanting to display or work with dates before 31/12/1899. This is because the core eWare.dll is written in Delphi which calculates its dates from 30/12/1899.

A nice illustration of the problem is if you use a demo install of Sage CRM and attempt to enter a new opportunity. If you enter a date less than 31/12/1899 into the opportunity "close by" date field it will null the field.

So Sage CRM has a limit of 1899, and SQL Server a limit of 1753, and when you realise that according to the Islamic dating system this year (2008) is actually 1428 both Sage CRM and SQL Server are 'busted'.

Even if we could store the correct Islamic year in the database you would not want the Sage CRM to be assuming that Hijri years are in fact Gregorian ones when you do date calculations. The number of year is only part of the issue there are different month lengths and year lengths to worry about. Remember the rhyme above, "Thirty days hath September... etc".

What we would need to do is convert them properly from the format that the database uses internally to the Islamic format.

Fortunately, it is quite easy to have dates in Sage CRM displayed in Hijri form but to have them maintained using the Gregorian form is much more difficult. And because I have only had limited time I am only going to consider the display of dates and not the maintenance using Hijri form. This would be possible but would require lots of programming effort put into the user interface.

The display can be tackled in two ways, Lists that include Dates and Summary/Edit/Workflow Screens that contain dates.

1) Lists

To have Hijri equivalents of Gregorian dates displayed in lists we would need to change the views on which the lists are based.

I have discussed earlier the way in which we can edit a view so it includes a new field derived from a calculation and have this field displayed on screen.

https://community.sagecrm.com/blogs/hints_tips_and_tricks/archive/2008/08/05/custom-edits-and-derived-fields-created-in-views.aspx

SQL Server has an easy way of calculating the Hirji equivalent of a Gregorian date.

The conversion is carried out using the CONVERT function. There are two CONVERT styles that are available in SQL Server to support Hijri dates:

• 130 - Returns the date via the Hijri calendar, in dd mon yyyy hh:mi:ss:mmmAM format.
• 131 - Returns the date via the Hijri calendar, in dd/mm/yy hh:mi:ss:mmmAM format.

So to convert a date to Hijri format in SQL, you could use this syntax:

SELECT CONVERT(nchar, GETDATE(), 131)

This query will return a string such as the following in its result set:

6/12/1429 5:48:11:950PM

An existing view like the vListOpportunity looks like

SELECT RTRIM(ISNULL(dbo.Person.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.Person.Pers_LastName, '')) AS Pers_FullName, dbo.Person.Pers_PersonId,
dbo.Person.Pers_CreatedBy, dbo.Person.pers_SecTerr, dbo.Person.Pers_PrimaryUserId, dbo.Person.Pers_ChannelID,
dbo.Person.Pers_EmailAddress, dbo.Company.Comp_Name, dbo.Company.Comp_CompanyId, dbo.Company.Comp_CreatedBy,
dbo.Company.Comp_SecTerr, dbo.Company.Comp_PrimaryUserId, dbo.Company.Comp_ChannelID, dbo.Channel.Chan_ChannelId,
dbo.Channel.Chan_Description, dbo.Company.Comp_EmailAddress, dbo.Opportunity.Oppo_OpportunityId, dbo.Opportunity.Oppo_PrimaryCompanyId,
dbo.Opportunity.Oppo_PrimaryPersonId, dbo.Opportunity.Oppo_AssignedUserId, dbo.Opportunity.Oppo_ChannelId, dbo.Opportunity.Oppo_Description,
dbo.Opportunity.Oppo_Type, dbo.Opportunity.Oppo_Product, dbo.Opportunity.Oppo_Source, dbo.Opportunity.Oppo_Note,
dbo.Opportunity.Oppo_CustomerRef, dbo.Opportunity.Oppo_Opened, dbo.Opportunity.Oppo_Closed, dbo.Opportunity.Oppo_Status,
dbo.Opportunity.Oppo_Stage, dbo.Opportunity.Oppo_Forecast, dbo.Opportunity.Oppo_Certainty, dbo.Opportunity.Oppo_Priority,
dbo.Opportunity.Oppo_TargetClose, dbo.Opportunity.Oppo_CreatedBy, dbo.Opportunity.Oppo_CreatedDate, dbo.Opportunity.Oppo_UpdatedBy,
dbo.Opportunity.Oppo_UpdatedDate, dbo.Opportunity.Oppo_TimeStamp, dbo.Opportunity.Oppo_Deleted, dbo.Opportunity.Oppo_Total,
dbo.Opportunity.Oppo_NotifyTime, dbo.Opportunity.Oppo_SMSSent, dbo.Opportunity.Oppo_WaveItemId, dbo.Opportunity.Oppo_SegmentID,
dbo.Opportunity.Oppo_SecTerr, dbo.Opportunity.Oppo_WorkflowId, dbo.Opportunity.Oppo_LeadID, dbo.Opportunity.Oppo_Forecast_CID,
dbo.Opportunity.Oppo_Total_CID, dbo.Opportunity.oppo_scenario, dbo.Opportunity.oppo_decisiontimeframe, dbo.Opportunity.oppo_Currency,
dbo.Opportunity.oppo_TotalOrders_CID, dbo.Opportunity.oppo_TotalOrders, dbo.Opportunity.oppo_totalQuotes_CID, dbo.Opportunity.oppo_totalQuotes,
dbo.Opportunity.oppo_NoDiscAmtSum, dbo.Opportunity.oppo_NoDiscAmtSum_CID, dbo.Opportunity.Oppo_PrimaryAccountId,
COALESCE (dbo.Opportunity.Oppo_Forecast, 0) * COALESCE (dbo.Opportunity.Oppo_Certainty, 0) / 100 AS Oppo_Weighted
FROM dbo.Opportunity LEFT OUTER JOIN
dbo.Person ON dbo.Person.Pers_PersonId = dbo.Opportunity.Oppo_PrimaryPersonId LEFT OUTER JOIN
dbo.Company ON dbo.Company.Comp_CompanyId = dbo.Opportunity.Oppo_PrimaryCompanyId LEFT OUTER JOIN
dbo.Channel ON dbo.Opportunity.Oppo_ChannelId = dbo.Channel.Chan_ChannelId
WHERE (dbo.Opportunity.Oppo_Deleted IS NULL)

so...

CONVERT(nchar, dbo.Opportunity.Oppo_TargetClose, 131) as Oppo_TargetCloseHijri

And we could change the vListOpportunity view to read

SELECT RTRIM(ISNULL(dbo.Person.Pers_FirstName, '')) + ' ' + RTRIM(ISNULL(dbo.Person.Pers_LastName, '')) AS Pers_FullName, dbo.Person.Pers_PersonId,
dbo.Person.Pers_CreatedBy, dbo.Person.pers_SecTerr, dbo.Person.Pers_PrimaryUserId, dbo.Person.Pers_ChannelID,
dbo.Person.Pers_EmailAddress, dbo.Company.Comp_Name, dbo.Company.Comp_CompanyId, dbo.Company.Comp_CreatedBy,
dbo.Company.Comp_SecTerr, dbo.Company.Comp_PrimaryUserId, dbo.Company.Comp_ChannelID, dbo.Channel.Chan_ChannelId,
dbo.Channel.Chan_Description, dbo.Company.Comp_EmailAddress, dbo.Opportunity.Oppo_OpportunityId, dbo.Opportunity.Oppo_PrimaryCompanyId,
dbo.Opportunity.Oppo_PrimaryPersonId, dbo.Opportunity.Oppo_AssignedUserId, dbo.Opportunity.Oppo_ChannelId, dbo.Opportunity.Oppo_Description,
dbo.Opportunity.Oppo_Type, dbo.Opportunity.Oppo_Product, dbo.Opportunity.Oppo_Source, dbo.Opportunity.Oppo_Note,
dbo.Opportunity.Oppo_CustomerRef, dbo.Opportunity.Oppo_Opened, dbo.Opportunity.Oppo_Closed, dbo.Opportunity.Oppo_Status,
dbo.Opportunity.Oppo_Stage, dbo.Opportunity.Oppo_Forecast, dbo.Opportunity.Oppo_Certainty, dbo.Opportunity.Oppo_Priority,
dbo.Opportunity.Oppo_TargetClose, CONVERT(nchar, dbo.Opportunity.Oppo_TargetClose, 131) as Oppo_TargetCloseHijri, dbo.Opportunity.Oppo_CreatedBy, dbo.Opportunity.Oppo_CreatedDate, dbo.Opportunity.Oppo_UpdatedBy,
dbo.Opportunity.Oppo_UpdatedDate, dbo.Opportunity.Oppo_TimeStamp, dbo.Opportunity.Oppo_Deleted, dbo.Opportunity.Oppo_Total,
dbo.Opportunity.Oppo_NotifyTime, dbo.Opportunity.Oppo_SMSSent, dbo.Opportunity.Oppo_WaveItemId, dbo.Opportunity.Oppo_SegmentID,
dbo.Opportunity.Oppo_SecTerr, dbo.Opportunity.Oppo_WorkflowId, dbo.Opportunity.Oppo_LeadID, dbo.Opportunity.Oppo_Forecast_CID,
dbo.Opportunity.Oppo_Total_CID, dbo.Opportunity.oppo_scenario, dbo.Opportunity.oppo_decisiontimeframe, dbo.Opportunity.oppo_Currency,
dbo.Opportunity.oppo_TotalOrders_CID, dbo.Opportunity.oppo_TotalOrders, dbo.Opportunity.oppo_totalQuotes_CID, dbo.Opportunity.oppo_totalQuotes,
dbo.Opportunity.oppo_NoDiscAmtSum, dbo.Opportunity.oppo_NoDiscAmtSum_CID, dbo.Opportunity.Oppo_PrimaryAccountId,
COALESCE (dbo.Opportunity.Oppo_Forecast, 0) * COALESCE (dbo.Opportunity.Oppo_Certainty, 0) / 100 AS Oppo_Weighted
FROM dbo.Opportunity LEFT OUTER JOIN
dbo.Person ON dbo.Person.Pers_PersonId = dbo.Opportunity.Oppo_PrimaryPersonId LEFT OUTER JOIN
dbo.Company ON dbo.Company.Comp_CompanyId = dbo.Opportunity.Oppo_PrimaryCompanyId LEFT OUTER JOIN
dbo.Channel ON dbo.Opportunity.Oppo_ChannelId = dbo.Channel.Chan_ChannelId
WHERE (dbo.Opportunity.Oppo_Deleted IS NULL)

2) Screen calculation of the date equivalent

There is a long-established technique of using dummy fields to facilitate the display of calculated information. You can see this in this article that discusses using a create script to produce derived data:

https://www.sagecity.com/sage-global-solutions/sage-crm/b/sage-crm-hints-tips-and-tricks/posts/how-do-i-put-calculated-or-derived-info-in-a-screen-s-top-content

The date conversion in Summary screens could be done either by a create script similar to the above technique or by a clientside script contained in the custom content.

Either way, you would need to carry out the conversion using Javascript.

This could be adapted to be used in either create scripts or clientside scripts

<script language="JavaScript">

var fixd;

function isGregLeapYear(year)
{
return year%4 == 0 && year%100 != 0 || year%400 == 0;
}


function gregToFixed(year, month, day)
{
var a = Math.floor((year - 1) / 4);
var b = Math.floor((year - 1) / 100);
var c = Math.floor((year - 1) / 400);
var d = Math.floor((367 * month - 362) / 12);

if (month <= 2)
e = 0;
else if (month > 2 && isGregLeapYear(year))
e = -1;
else
e = -2;

return 1 - 1 + 365 * (year - 1) + a - b + c + d + e + day;
}

function Hijri(year, month, day)
{
this.year = year;
this.month = month;
this.day = day;
this.toFixed = hijriToFixed;
this.toString = hijriToString;
}

function hijriToFixed()
{
return this.day + Math.ceil(29.5 * (this.month - 1)) + (this.year - 1) * 354 +
Math.floor((3 + 11 * this.year) / 30) + 227015 - 1;
}

function hijriToString()
{
var months = new Array("Muharram","Safar","Rabi-al Awwal","Rabi-al Thani","Jumada al-Ula","Jumada al-Thani","Rajab","Sha\'ban","Ramadhan","Shawwal","Dh ul Qa\'dah","Dhul Hijjah");
return this.day + " " + months[this.month - 1]+ " " + this.year;
}

function fixedToHijri(f)
{
var i=new Hijri(1100, 1, 1);
i.year = Math.floor((30 * (f - 227015) + 10646) / 10631);
var i2=new Hijri(i.year, 1, 1);
var m = Math.ceil((f - 29 - i2.toFixed()) / 29.5) + 1;
i.month = Math.min(m, 12);
i2.year = i.year;
i2.month = i.month;
i2.day = 1;
i.day = f - i2.toFixed() + 1;
return i;
}

var tod=new Date();
var weekday=new Array("Sunday","Monday","Tuesday","Wednesday","Thu rsday","Friday","Saturday");
var monthname=new Array("January","February","March","April","May"," June","July","August","September","October","November","December");

var y = tod.getFullYear();
var m = tod.getMonth();
var d = tod.getDate();
var dow = tod.getDay();
document.write(weekday[dow] + " " + d + " " + monthname[m] + " " + y);
m++;
fixd=gregToFixed(y, m, d);
var h=new Hijri(1421, 11, 28);
h = fixedToHijri(fixd);
document.write(" CE | " + h.toString() + " AH   ");

</script>

Warning.

Although this article should point you in the right direction, please do not take my usage of the date calculations as foolproof. If you want to calculate Islamic dates then you must check, recheck and double-check the calculations against a trusted reliable official qualified source. The script and functions I have used here are only estimates of the visibility of the lunar crescent, and the Islamic month may actually start 1-2 days earlier or later than predicted.