Thursday, February 19, 2009

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/

15 comments:

Jivtesh Singh said...

Awesome Post, and amazing history and introduction to go along with it! Loved learning about this.

Thanks!

Mariano Gomez said...

Jivtesh,

Thanks for the kind words. This is one of the posts I believe I will come to love over the years.

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

Doug Pitcher said...

Amazing that you could actually get me hooked enough to read the whole post. I don't have any need for this (so far) but loved the blog. I'd keep this in your favorite posts lists for sure.

Anonymous said...

Mariano ... what about Um Al kura Date its more Fixed solary than Hijri date thats make it more required at Saudi Arabia .. can we see a post about it - and please make more explanation about VBA when it comes to be used in your post(as my favorit it Dexterity language) thank you..

Mariano Gomez said...

SQL Server does not have direct support for Um al-Qura calendar, which is more intended for civil events. However, there is an implementation Um al-Qura in the .NET Framework which would allow you to implement a CLR type function for SQL Server.

You can take a look at how to do this at http://blogs.msdn.com/vsarabic/archive/2009/02/12/sql-server-umalqura-calendar-as-a-clr-type.aspx

Likewise, you could create a .NET assembly which can be wrapped around a COM object to use directly in VBA.

Best regards,

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

Anonymous said...

Hello Mariano,

Im developing a web application
system that converts english date to hijiri date. So the requirements must be in javascript...can you help on this stuff :))))? Thanks!

Faisal said...

Sorry for interrupting .... I need help.

I am working on an application where I would have to save English date and its equivalent Hijri date (base on Um Al Qura Calendar). I have perform search operations based on both English and Hijri dates.

- Should I use have two columns in my table; one for English date and one for Hijri date?
- Should I use DateTime CLR data type for both dates or?

I will wait your email.

Regards

Faisal
Jeddah, Saudi Arabia

Mariano Gomez said...

Faisal,

You certainly need to create a CLR function for this. Take a look at the following post and make sure to follow the comments too:

http://blogs.msdn.com/vsarabic/archive/2009/02/12/sql-server-umalqura-calendar-as-a-clr-type.aspx

MG.-
Mariano Gomez, MVP

Hamada M. Ashour said...

it's a first step, you need to handle some scinarios like that transactin date can't accept date not exist in the current fiscal year, so i think you need to change the schema of each windows table and adding a new field, but its ofcourse danger for new updates and service packs, call me if you want more info

BTW, good work

Mariano Gomez said...

Hamada,

Thanks for the input! I am sure there are a lot of "gotchas" with Hijri dates, but as you said, a first step nonetheless. The good thing is enhancements can come one you have the basic idea.

Keep up the readership!

MG.-

Sanjay Kumar Rajarao said...

Hi Mariano,

Do you have any example to custom validate date based on fiscal periods.

Thank you
Sanjay

Mariano Gomez said...

Sanjay,

Can you provide an example of what you mean?

MG.-
Mariano Gomez, MVP

Sanjay Kumar Rajarao said...

Hi Mariano,

Thanks of for your reply, reading one article a day for your blog, missed to respond on this one.

Wanted to know if we can validated the doc dates on AP transaction just to make sure if doc date entered is within the GP Fiscal periods.

Lesslie Vail at community directed to check doc verify tools of PSTL, which suits the requirements.

Thanks -
Regards
Sanjay

date said...

Thanks of for your reply, reading one article a day for your blog, missed to respond on this one.
me too

Evincible Solutions said...

Wow, thats amazing work bro. Innovation should be appreciated. Thanks for sharing this blog.
Microsoft Dynamics GP