Thursday, September 16, 2010

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.


  1. Maybe you can point me in the right direction?! (may not be related to this post) I get the following error when installing from simple scripts:

    Parse error: syntax error, unexpected ',' in
    /home2/revenuea/public_html/intranet/crm/include/SugarLogger/php.ini on line
    Fatal error: Call to a member function info() on a non-object in
    hp on line 92
    Bluehost said: "The issue is your account has 15336 php.ini files. If you use one php.ini file, and change it to single.php .ini, that may fix it, but it may break all that you've set up with those php.ini files."

  2. @John

    Sorry, no idea. I'd have to look at your PHP.INI file and the fact your provider says you have over 15K iterations of it makes me squirm at the thought of doing so.

  3. Hello to everybody.

    May be you can try to use a webservice to comunicate SugarCRM with your C# aplication.

    You can find more information at the SugarCRM developement guide.


  4. Angel,

    This post has allowed me to stop pulling out what's left of my hair. My date entries are finally working thanks to you. Thanks so much!!



Your comments, feedback and suggestions are welcome, but please refrain from using offensive language and/or berating others. Thank you in advance.