Convert Elliott Internal Date to Proper Date Format in Third-Party Tools (Crystal Reports)
Version: 8.5 & Up
ELIDATE2SQL() User-Defined Function
Elliott's internal database stores a date field as an 8-digit numeric in a YYYYMMdd format where YYYY is the 4-digit year, MM is the number of the month, and dd is for the day of the month. For example, a date of 07/08/2024 is stored internally in Elliott as 20240708.
In third-party applications, like Crystal Reports or Excel, you can certainly use the YYYYMMdd value as it is, but if you prefer to convert this value to a real date value, you can also do that. This article explains a user-definable function we created since the Elliott 8.5 release, ELIDATE2SQL().
In the Elliott 8.5 DDF, we introduced several user-definable functions. One of them is ELIDATE2SQL(). See the following KB article: https://support.elliott.com/knowledgebase/articles/1827145-new-user-defined-functions-in-elliott-ddf
PSQL Control Center
The following is an example of how you can use the SQL statement to access Elliott data:
SELECT CUS_NO, CUS_NAME, CUS_START_DT, ELIDATE2SQL(CUS_START_DT) FROM ARCUSFIL;
The following is the result:
The following is the result:
Crystal Reports
The following are specific examples of using this ELIDATE2SQL function in Crystal Report:
Confirm You Have the Right DDF
In Crystal Report Writer -> pull-down menu (Database) -> Database Expert -> My Connection -> ODBC -> Elliottdata96 (whatever the database name that's applicable to you) -> Stored Procedures.
Then you will see all user-defined functions. See sample screen below: 
This is just confirming that CRW recognizes those functions. If you can see the ELIDATE2SQL functions, then you have the right DDF. Otherwise, your DDF files are older than 2018 and you need to update. You can speak to Netcellent support or your resellers if you need to update your DDF. Close this window then go to the next session.
Use SQL Expression Fields in Crystal
For example, if we want to convert INV_DATE to DATE format from numeric, we would click "Field Explore" on the toolbar, then right-click "SQL Expression Fields" and click "New" to assign a new name (i.e., Inv-Date (SQL)). See sample screen below:
SQL Expression Editor
Creating Statement from SQL Expression Editor: After clicking "OK" for the new name (Inv-Date (SQL)), you will see the editor screen as shown below. Enter ELIDATE2SQL () manually, then click INV_DATE from the top box to insert the field in parentheses ().
Click Check Function (X-2) to verify there are no errors, then save this new field.
Click Check Function (X-2) to verify there are no errors, then save this new field.
Insert SQL Expression Fields on Report
The new field "Inv-Date (SQL)" will be available as the category SQL Expression Fields. You may insert the new field in the report (in column 3 – Date (SQL)). Column 2 (Date (numeric)) is the original number format for comparison purposes.
RSS/EMK