Kbase P130818: How do I query separate date and time fields in a OpenEdge database using EasyAsk?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/23/2008 |
|
Status: Unverified
GOAL:
How do I query separate date and time fields in a OpenEdge database using EasyAsk?
GOAL:
How do I combine date and time information in different fields from an OpenEdge database using EasyAsk?
FACT(s) (Environment):
EasyAsk 9.7
FIX:
Many older OpenEdge databases represent date and time information with a separate date field and time field rather than a single date/time field.
For example, a database table might contain a date field called "StartDate", and an integer field called "StartTime", which represents the
time of day as the number of seconds since midnight.
EasyAsk can answer sophisticated questions against date/time fields like "Show me who started yesterday before 10 am", but to do so
requires adding a formula to the EasyAsk dictionary that converts the separate date and time fields into a single date/time field.
The formula will require the use of an OpenEdge conversion function that is not normally part of the EasyAsk dictionary, and must be
manually added to it.
For example, if the EasyAsk name for the "StartDate" field is Start Date Without Time, and that the EasyAsk name for the "StartTime" field is
Start Time In Seconds, then do the following:
In a text editor like Notepad, open the EasyAsk dictionary (.dxp) file.
Find the definitions for the "to integer" and "to number" functions.
Just below those definitions, add the following line:
FUNCTION Name=to timestamp;Def=CAST(@ as TIMESTAMP)D;Type=D;Part=F;Tier=0;Keys=
Save the dictionary file and exit the text editor.
Open the dictionary in the DataWorkshop, and in the Dictionary Editor add the formula Start Date (which users' queries will actually be referencing), with the definition:
add days(Start Time In Seconds * 1000, to timestamp(Start Date Without Time))
Change the Display Format property for the formula to something appropriate like: mm/dd/yyyy hh:mm AM/PM
Add the formula to the definition of "when" (and remove the Start Date Without Time column, if it was previously included).
Save the dictionary in DataWorkshop.