Convert Elliott Internal Date to Proper Date Format in Third-Party Tools (Crystal Reports)

Release Date: 07/08/2024

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.netcellent.com/portal/en/kb/articles/convert-elliott-internal-date-to-proper-date-format-in-third-party-tools-crystal-reports

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:

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.

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

• Related Articles

• Integrate with Third-Party Shipping Manifest System

  'Q - Can I integrate a third party shipping manifest system with Elliott Business Software? A - Yes, you can. Our recommended solution is Starship by V-Technologies. It has built-in integration with Elliott Business Software out of the box. The base ...

• Converting Elliott Internal Date to Conventional Date Format in Excel

  Q - If I retrieve Elliott's data in Excel through ODBC, the date column is retrieved in an 8-digit numeric format like:20161231, where the first 4 digits are the years, the following 2 digits are the month, and the last two digits are the day of the ...

• Third Party ACH Payment Software to Support Elliott AP (Accounts Payable)

  Release Date: 9/28/2020 Revised Date: 11/30/2022 Version: 8.5 Q - Do you support any third party ACH software that will work with Elliott's Accounts Payable module? A - We have a few customers who has implemented the following solution: ACH Universal ...

• Feature - Elliott 3rd Party WMS or Logistic Support

  Release Date; 8/25/23 Revised: 5/16/24 Version: 8.5 and Above The purpose of this feature is to provide 3rd Party WSM, or 3rd Party Logistic (3PL) support. In this document, we will interchange the terminology of 3rd Party WMS and 3PL. This feature ...

• Anti-Virus Caused Failure on Crystal Reports ODBC Access

  Release Date: 05/22/18 Q - I have a Crystal Report that has ran against our production Elliott data without issue. I am trying to run this same report against the test Elliott data. I am getting "failed to retrieve data" messages when trying to run ...