Saleslogix time stamp translation
Posted By: nicocrm on May 28th, 2008 in Saleslogix
No Gravatar

Normally, using the Saleslogix provider automatically translates dates from UTC to local time and vice versa.  For example, if I write "2008/05/12 4:00:00" to the database the provider will automatically convert this to 9:00:00 (I am at GMT-5), and when I read it I will get the original value.  If you use a literal in SQL you have to make sure it conforms to the Saleslogix oddball format (a mix of 2 different ISO formats): "yyyymmdd hh:nn:ss".  In your Saleslogix scripts this can be done with Application.BasicFunctions.DateToIso.  Test it out by running a query such as "select * from contact where createdate = ’20080502 11:00:00′" – the SQL profiler will show the date passed as 16:00:00.

You can turn this off on a field by field by this by toggling the DateTimeType field in the SecTableDefs table to "D" for the field in question (this can also be done by changing the field type to "Date" in the DB Admin).  For example if you run "select * from opportunity where actualclose =  ’20080102 11:00:00′" the profiler shows the time as "11:00:00".

OK now that we got this brief recap out of the way let us see what this looks like on the .NET side of things.

Literal Parameters

If you pass the date as a literal in SQL using the correct format everything works as described above.

Command Parameter in ADO.NET

Usually you will use something like this:

cmd.CommandText = "SELECT COUNT(*) FROM OPPORTUNITY WHERE ACTUALCLOSE = ?";
cmd.Parameters.Add("p1", OleDbType.DBTimeStamp).Value = new DateTime(2008, 5, 12, 11, 0, 0);
int result = (int)cmd.ExecuteScalar();

This works as expected: as long as the type of the parameter is DBTimeStamp Saleslogix will convert it by default, unless it realizes that the parameter relates to a field for which conversion is disabled.

You need to realize that the Saleslogix provider will only be able to determine which field the parameter applies to in the most common case.  For example the following query throws it off guard:

cmd.CommandText = "SELECT COUNT(*) FROM OPPORTUNITY [A1] WHERE [A1].ACTUALCLOSE = ?";
cmd.Parameters.Add("p1", OleDbType.DBTimeStamp).Value = new DateTime(2008, 5, 11, 11, 0, 0);
int result = (int)cmd.ExecuteScalar();

In that case the date is passed to SQL as "16:00:00": it accidentally got converted to GMT.  Had me stumped for a while this morning until I realized that the SLX provider was not understanding my square brackets.

Make sure you do use a DBTimeStamp parameter otherwise in addition to having the date not converted properly you may have problems in the sync to remotes.

HQL Parameter to NHibernate

Things are pretty smooth in NHibernate (as you would expect since it does not use complex queries).  The following works as expected and does not convert the date parameter:

using (NHibernate.ISession sess = new SessionScopeWrapper(false))
{
  IList result = sess.CreateQuery("from Sage.SalesLogix.Entities.Opportunity where actualclose=?")
    .SetDateTime(0, new DateTime(2008, 5, 12, 11, 0, 0))
    .List();
}

The following works as expected and converts it to GMT:

using (NHibernate.ISession sess = new SessionScopeWrapper(false))
{
  IList result = sess.CreateQuery("from Sage.SalesLogix.Entities.Opportunity where createdate=?")
    .SetDateTime(0, new DateTime(2008, 5, 12, 11, 0, 0))
    .List();
}

Obviously if you make the HQL more complex you can probably still find a way to throw it off.

Sage Criteria API

I have not tested this but I would assume it works just as well as HQL.  By the way, some people are scared of using HQL because of not being the API "officially" pushed by Sage.  However, consider this:

  • Is it likely that Sage will move off NHibernate?  It is possible but would be a pretty big step.  I should hope they make better use of their time.
  • Internally the Sage assemblies use HQL without remorse.  One more reason to doubt that they will make a shift.
  • Consider the time saved versus using the Criteria API.  Not that the Criteria API is not good in some cases (search screens come to mind) but most of the time it is just a gigantic hurdle – both in terms of learning curve and in terms of lines of code needed for the same result.  We all know how to express ourselves in SQL and the jump to HQL is a very small one.

Leave a Reply