Date Formats
From Alchemex 7.1
Home > Connector > Maintaining the Connector > Date Formats
Date Formats
Different database systems store and recognise dates in different formats. Sage Intelligence Reporting attempts to hide this detail from its end users by translating dates at run time into a recognizable format before passing the date to the underlying Database System. In order for Sage Intelligence Reporting to know how the Database System is expecting the dates, the Data Connection (property Date Format) in the Connector is pre configured. Additionally, different Database Systems expect dates to be delimited with different characters. This is also configured on the Data Connection object as the property Date Delimiter.
For the end user dates in report Parameter boxes can always be picked using the Sage Intelligence Reporting Calendar thus hiding the common problems that occur with ambiguous date formats. The list below shows date formats that are recognized by some of the more common Database Systems.
| 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 | ' |
Note: Changing these settings on a Connection or Connection Type object can cause report execution problems and may cause incorrect date ranges to be returned. For supported Connection types in the Sage Intelligence Reporting Connector you should not change the settings for these fields.
Selecting Dates in Report Parameters
It is preferable to use the Sage Intelligence Reporting Calendar when selecting Dates in Parameters. If you prefer to key in Date values for a Report Parameter or Filter it is best to use the format dd mmm yyyy to avoid ambiguity and Date errors. for example, 19 January 2008 OR 19 Jan 2008.
In this way Sage Intelligence Reporting can unambiguously translate the date into the format expected by the underlying Database system. The Sage Intelligence Reporting Calendar is shown below:
