Omega - Support Information

Page Updated: 7-3-14

Reporting - Date Format Conversion

As anyone who has to create or maintain Omega reports discovers eventually, the application holds dates in a non-standard internal format. The value is held as a 2-byte unsigned integer and is a count of the number of days since 29th February 1948. This date format was chosen when Omega was designed for several reasons, including database query and rules engine calculation performance.

Unfortunately there are a number of problems with using this representation:

  • Parameter-based queries in reports such as Crystal Reports, where the user is prompted to enter a date, must be in the usual dd/mm/yyy-style format. Users cannot be expected to know that 20780 represents 20th January 2005.
  • Converting between the two is not a simple mental-arithmetic task when testing a report or using a tool such as the Pervasive Control Center's SQL Query tool.

To solve the first issue there is a table within the database (sys_dateconv_table or sys_dateweek_table) to convert between the two formats and include in the report, though it can result in a performance hit depending on report complexity. To help the administrator with the second issue a number of solutions are described below:

Query the Omega database
There are two tables, SYS_DATECONV_TABLE and SYS_DATEWEEK_TABLE, that cross-reference Crystal and Omega dates for every day between 1st January 1980 and the 31st December 2030. SYS_DATEWEEK_TABLE was introduced later and also contains week numbers. This table can be accessed via the Pervasive Control Center or a simple report.

Date Conversion Table
The SYS_DATECONV_TABLE Btrieve and Omega date formats have been exported to an ASCII text file.

Date Conversion Utility
There is a utility available for downloading (Date Conversion Calculator) to convert between the two formats. This utility has been tested on Windows NT and XP, but should work on any Windows platform that supports 32-bit applications. Contact Support4Omega for more details.

Web site Conversion Tool.
.

This site is copyright 2003-2017 Neil Hughes. All rights reserved.
Company and product names on this site may be trademarks or registered trademarks and are hereby acknowledged.

Contact (Support)
Email: support@support4omega.co.uk
Skype: support4omega

Contact (Website/other)
Email: admin@support4omega.co.uk