SugarCRM Cookbook: .NET and Dates

A user of my CandyWrapper library recently requested some help addressing a problem they were experiencing with the insertion of dates into SugarCRM via C#.


After some investigation, I identified the source of the problem was the format in which the date was being submitted to the SugarCRM SOAP API.  


The interaction reminded me of the frequency at which this particular problem appears within the SugarCRM Forums.  Its actually rather understandable as there isn't much documentation covering the subject.  


If you are also a C# developer, the code snippet that solved the problem is provided below:

01 DateTime dtTemp = System.DateTime.Now;
02 dtTemp = TimeZone.CurrentTimeZone.ToUniversalTime(dtTemp);
03 string sDate = String.Format("{0:yyyy-MM-dd HH:mm:ss}", dtTemp);


This is the standard way in which I handle dates in my C# projects, but there is a special twist to my code which is worth highlighting and might help save you some additional frustration in your projects.


Let us analyze it, line by line.
Line 01 should be self-explanatory.  It merely gives us the current date and time and assigns the value to the variable dtTemp.


Line 02 is the special twist and I would recommend incorporating it into your coding techniques one way or another.  First, lets describe what it does.  


The line takes the current date and time assigned to the variable dtTemp in line 01 and converts it to Universal Time Coordinate (UTC), but not before compensating for the current computer's defined time zone.


Why would I even bother doing this?  There is a very good reason for it.  


SugarCRM utilizes UTC formats for all its dates.  This allows it to support users in varying time zones and automatically compensates for the time difference between UTC and the user's selected time zone (stored in the user's preferences).  


Were we to insert a date and time stamp without first compensating for this behavior, the date and times we see in SugarCRM would be inaccurate.  For example, if we insert a date and time of: 17-Sept-2010 06:00 AM, it would actually appear as 16-Sept-2010 10:00 PM to a user within the U.S. Pacific Time Zone due to the 8 hour time difference.  Obviously, that is incorrect.  The date should have been entered in UTC format (17-Sept-2010 02:00 PM) in order to get it to properly appear within SugarCRM for all users, regardless of their selected time zone.


Lastly, line 03 formats the date and time in a manner that SugarCRM expects.  If we take the example just provided, the resulting format would be: 2010-09-17 14:00:00


Give it a try.