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.

This is a subject that I have now learnt far more about than I ever thought I would ever want to.

Please note that this posting can't be regarded as an authorative 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 of 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 uses.

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 that 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 if year is only part of the issue there 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

Sage CRM 6.2 supports SQL Server 2000, 2005 and 2008.  These versions of SQL Server all have a 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 2000 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)

Of course that is deeply unpleasant to look at but we can now add in another column to calculate the hijri equivalant of the Oppo_TargetClose field

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://community.sagecrm.com/blogs/hints_tips_and_tricks/archive/2008/11/20/how-do-i-put-calculated-or-derived-info-in-a-screen-s-top-content.aspx

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.  Below is some useful code that I lifted from the Muslim Programming and Design Community website.  For ease of reading I have included it here.

http://forums.mpadc.com/showthread.php/hijri-date-displayer-3232.html?s=df8c30ae603b046f5e0ba4e4cc6e88c1&t=3232

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 &nbsp;&nbsp;");

</script>

Warning.

Although this article should point you in the right direction, please do not take my usage of the date calculations as gospel.  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.

So there.