Kbase P3115: No records are retrieved when running a query containing the TODAY function in the WHERE clause agai
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
11/05/2007 |
|
Status: Unverified
FACT(s) (Environment):
Progress 9.1x
OpenEdge 10.0x
OpenEdge 10.1A
Windows NT 32 Intel/Windows 2000
MS SQL DataServer
MS SQL
SYMPTOM(s):
No records are retrieved when running a query containing the TODAY function in the WHERE clause
Record has been created with the GETDATE () function.
The field in the WHERE clause is a DateTime data type.
CAUSE:
Bug# OE00077268
CAUSE:
When you do an equality check, it must be exact. By inserting the DateTime field with the function GETDATE(), the time will be part of the date. When the date is DISPLAYED the time will be truncated. Since the TODAY function only returns the date, it will never equal the GETDATE() value.
FIX:
Upgrade to OpenEdge 10.1B or later.
If upgrading is not an option, there are 2 ways to work around this limit:
1. Use a query similar to below:
FIND table WHERE STRING(date) BEGINS STRING(TODAY).
This would eliminate the equality check and they would not have to know what time was part of the record.
2. Use a query similar to below:
FOR EACH myTable NO-LOCK WHERE startDate > TODAY - 1
AND startDate < TODAY + 1:
DISP myTable.
END.