In this example I want to explore how Sage CRM's SOAP web services interact with dates and times.  The background for this article is the experience that a user may have within the main interface.  

As we can see in the image below this user has opted in their user preferences to work with date/time fields expressed as AM/PM.

The very short summary article is that these user preferences only matter to date/time fields when they are experience through the User Interface.  The SOAP web service interface is not  concern about user preferences as dates and times are always passed in one format.  This is either expressed in the server Time Zone or as Coordinated Universal Time (UTC).

The SOAP web services interface allows a user's userid and password to generate a secondary logon to Sage CRM.  SOAP web services are routed through the eWare.dll and they create a Session in the same way that a user creates a session when they logon through the interface.

You can see this second session when you look at the SysAdmin page.

http://[servername]/[instancename]/eware.dll/sysadmin

The second session has a session id just like it does though the main interface.  Can you note that in the image above the web services have created a session with an ID 35972304925488.


 I can actually use that SID in the main interface.


When a Session ID created by Web Services is used to generate a main interface screen then the user preferences are followed.  But this is because the ability to read preferences comes from the overall context of using the session within the browser based interface.

Those user preferences missing completely in the context of a web services transaction.  And this is down to the WSDL.

The definition of the user object in the WSDL is very limited. (http://[servername]/[instancename]/eware.dll/webservices/webservice.wsdl).

<complexType name="users">
<complexContent>
<extension base="typens:ewarebase">
<all>
<element name="createddate" minOccurs="0" maxOccurs="1" type="xsd:dateTime"/>
<element name="firstname" minOccurs="0" maxOccurs="1" type="xsd:string"/>
<element name="lastname" minOccurs="0" maxOccurs="1" type="xsd:string"/>
<element name="logon" minOccurs="0" maxOccurs="1" type="xsd:string"/>
<element name="updateddate" minOccurs="0" maxOccurs="1" type="xsd:dateTime"/>
<element name="userid" minOccurs="0" maxOccurs="1" type="xsd:int"/>
</all>
</extension>
</complexContent>
</complexType>

This means that user preferences about dates are not referenced in web service transactions.

As an example of this we can consider a new Task being inserted using web services.  The code below has been written in C#.


The important lines are

  • newTask.datetime = DateTime.Now;
  • newTask.datetimeSpecified = true;

The only configuration that controls date and time behaviour is within the web services settings page

Administration -> System -> Web Services

The setting "Send and return all dates and times in universal time" allows us to specify the format in which Sage CRM Web Services send and receive times and dates.

There are two possible values:

  • Yes - which specifies to use Coordinated Universal Time (UTC) as the record is saved.
  • No - which specifies to use the format set on the Sage CRM server as the record is saved. (This option is important when migrating users from different time zones.)

The C# code will result in the following web service message (that requests CRM to insert the data).

Jun 1 2017 7:32:45.324 19316 14612 5 Request Content : <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Header><SessionHeader xmlns="http://tempuri.org/type"><sessionId>35972304925488</sessionId></SessionHeader></soap:Header><soap:Body><add xmlns="http://tempuri.org/type"><entityname>communication</entityname><records xsi:type="communication"><action>ToDo</action><channelid>1</channelid><datetime>2017-06-01T07:32:45.3046913+01:00</datetime><description>New Web Service Task forGatecom Inc.</description><note>Contact Gatecom Inc. to discuss the web services interface</note><priority>Normal</priority><status>Pending</status><subject>New Web Service Task forGatecom Inc.</subject><type>Task</type></records></add></soap:Body></soap:Envelope>

The key element is <datetime>2017-06-01T07:32:45.3046913+01:00</datetime>.

It is then down to Sage CRM to use the UTC setting to decide how to adjust this in the database as it is saved.

Jun 1 2017 7:32:45.394 19316 14612 3 execsql,time,sql 16 INSERT INTO Communication(comm_action,comm_channelid,comm_datetime,comm_description,comm_note,comm_priority,comm_status,comm_subject,comm_type,comm_CreatedBy,comm_CreatedDate,comm_UpdatedBy,comm_UpdatedDate,comm_TimeStamp,comm_Secterr) VALUES (N'ToDo',1,'20170601 07:32:45',N'New Web Service Task forGatecom Inc.',N'Contact Gatecom Inc. to discuss the web services interface',N'Normal',N'Pending',N'New Web Service Task forGatecom Inc.',N'Task',1,'20170601 07:32:45',1,'20170601 07:32:45','20170601 07:32:45',-2147483640)

In this case the field to look at is the comm_datetime value.

Web Service settings only control how dates are saved. Once they are in the database there is no interaction with user preferences or settings. If you look at the first image included in this article you can see that the user can have the dates shown in AM/PM and the web services will have interacted with that data in the same way.