I recently ran into a client issue where several SQL views were built to support generation of some dashboard and reporting outputs. When retrieving this data, there was a need for filters based on the standard SalesLogix Opportunity EstimatedCloseDate field.

When testing it, it was observed that the date filter did not work correctly on the start date of the date range. So if an opportunity had a close date of '5/4/2011' it would only be pulled up in SalesLogix if the start date was '5/3/2011'.

Our first thought was that GMT was being applied, and therefore we needed to verify if the EstimatedCloseDate field was properly defined as a standard date field and not a DateTime field.

A query of the SECTABLEDEFS table pointed out that the field was correctly specified as a "D" indicating that this is a Date ONLY field and no GMT formatting should apply.

So the next step was to use the SalesLogix profiler. The SalesLogix profiler executable is located in the program filesSalesLogix folder and shows you the actual query that is being processed by SalesLogix. We noticed that the profiler was reporting that a GMT adjustment is being made when the query is processed against the SQL view that retrieves the opportunity information.

When we next checked the SECTABLEDEFS table for the definition of the SQLView, it turned out that the DATETIMETYPE was set to NULL by default when the View was created. This needed to be set to "D". Without this, the SalesLogix would assume that the field needs to have GMT applied to it. Changing the DateTimeType field to a "D" for the EstimatedCloseDate field used in the SQLView finally resolved the issue.

Bottom line - make sure to set your Date fields to the appropriate type values in SECTABLEDEFS table because SalesLogix does not automatically set them when they are initially created.