Displaying Hijri dates in Microsoft Dynamics GP

In the last few days, many of my friends in the Middle East have been asking how to display Hijri dates -- as opposed to Gregorian dates -- in Microsoft Dynamics GP. It has been among the latest topic on the Microsoft Dynamics GP Developer Community newsgroup as well, and of course, the object of my curiosity for the last few days.

First, a bit of background!

The Hijri calendar is used to date events and celebrate Islamic holidays and festivities. It is based on the lunar cycle, having 12 lunar months in a year of about 354 days. Because this lunar year is about 11 days shorter than the solar year, Islamic holidays, although celebrated on fixed dates in their own calendar, usually shift 11 days earlier each successive solar year, such as a year of the Gregorian calendar. Islamic years are also called Hijra years because the first year was the year during which the Hijra occurred—Islamic prophet Muhammad's emigration from Mecca to Medina. Thus each numbered year is designated either H or AH, the latter being the initials of the Latin anno Hegirae (in the year of the Hijra).

Microsoft Dexterity does not have native support for Hijri, but Microsoft SQL Server does out of the box. Support for Hijri in SQL Server is surrounded by legends, myths, and the stuff of science fiction. Legend has it that Microsoft sent an army of software engineers and architects to the Middle East to collect records and conduct statistical analysis on the Hijri calendar records from Kuwait and Saudi Arabia. The resulting algorithm embedded in many Microsoft products was denominated the Kuwaiti Algorithm. Whether this is truth or not is left to the folks at Microsoft.

The Solution

SQL Server Convert function provides support for Arabic style dates by using the Kuwaiti algorithm. The following query will return an Arabic style date for today's Gregorian date:

Hijri.sql

-- Hijry conversion of today's date

select convert(varchar, getdate(), 131)

The query result is as follows:

------------------------------
24/02/1430 10:34:02:710PM

(1 row(s) affected)

Given this, we can create a Visual Basic for Applications (VBA) user form in Microsoft Dynamics GP that will in turn format a query. We can then execute this query via ActiveX Data Objects (ADO). For this example, we will use the Receivables Transaction Entry window and will be adding a push button next to the date field with Modifier. Once we have the form changed, we will then add the window, Document Date field and the newly added push button to VBA. The user form will have the necessary code to convert the Gregorian transaction date to Hijri.

The following is the outcome of our project:

1) Custom window with push button to load user form















* click image to enlarge

2) User form displaying Gregorian date to Hijri













Downloads

The following links will allow you download the package files for this project:

v10 - DisplayHijri.Package: contains the modified Receivables Transaction Entry screen and the custom user form.

v10 - References.Package: contains references for Microsoft ActiveX Data Objects 2.6

Related Articles

How to Horizontally Flip the Windows - From the Translating Dexterity Applications series at Developing for Dynamics GP, by David Musgrave.

Until next post!

MG.-
Mariano Gomez, MVP, MCP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/