Kbase P134278: SQL: A query using the TO_TIMESTAMP function in the WHERE clause fails to retrieve a record.
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
15/08/2008 |
|
Status: Unverified
SYMPTOM(s):
SQL: A query using the TO_TIMESTAMP function in the WHERE clause fails to retrieve a record.
The query fails to retrieve the record when the WHERE clause is written as:
WHERE myDateTimeField = TO_TIMESTAMP('2008-08-11 13:57:57.99')
A record exists in the table whose DATETIME field value is equal to '2008-08-11 13:57:57.990'
The query retrieves the record when the WHERE clause is written as:
WHERE myDateTimeField = TO_TIMESTAMP('2008-08-11 13:57:57.990')
FACT(s) (Environment):
All Supported Operating Systems
OpenEdge 10.x
CAUSE:
The first fails because it is looking for a record where the milliseconds part of its myDateTimeField is 99 milliseconds which does NOT exist. The first succeeds because it is looking for a record where the milliseconds part of its myDateTimeField is 990 milliseconds which DOES exist.
FIX:
The TO_TIMESTAMP function takes a TIMESTAMP literal parameter that may be of the form: YYYY-MM-DD HH:MM:SS:SSS or YYYY-MM-DD HH:MM:SS.SSS. Ensure that the milliseconds part of TIMESTAMP literal is written accurately using the 999 format.