If you’re a user of the Sage Intelligence Connector Module, you’ll know that the Connector maintains the links between your Sage Intelligence reports and your Sage Business Management Solution data sources. Different database systems store and recognise dates in different formats, and Sage Intelligence attempts to hide this detail from you by translating dates at run time into a recognizable format before passing it to the underlying database system. In this tip, we help you better understand date formats in Sage Intelligence.
For Sage Intelligence to know how the database system is expecting the dates, a Date Format property is provided for a connection and is pre-configured. Additionally, different database systems expect dates to be delimited with different characters. This is also pre-configured through the Date Delimiter property.
The following list provides an example of date formats that are recognized by some of the more common database systems.
| Database | Date Format | Date Delimiter |
| Pervasive | M/DD/YYYY | ‘ |
| DBase | YYYY/MM/DD | # |
| SQL Server | DD-MMM-YYYY | ‘ |
| Access | M/DD/YYYY | # |
| Oracle | DD-MMM-YYYY | ‘ |
When using date parameters in a report, it is preferable to make use of the Sage Intelligence date picker to make your selections at run time. This helps avoid ambiguity and errors that are possible when entering dates.
The date picker is enabled for any parameter applied to a date field whose data type is set to a date type in the Connector. Examples of date types are DB Date and DB Timestamp.
Then, when you run a report and the parameter dialogue is shown, by clicking on the ellipses to the right of the field, the date picker is shown. If you prefer to key in date values for a parameter (or filter), it is best to use the format ‘dd mmm yyyy’, for example; ‘14 February 2018’ or ‘14 Feb 2018’. In this way, Sage Intelligence can correctly translate the date into the format expected by the underlying database system.
The post Understanding date formats in Sage Intelligence appeared first on Sage Intelligence.
Source: Sage Intelligence
