View Incident Report

Serious

Reported By: Bruce Vander Werf
Reported On: 9/30/2001
For: Version 2.11 Build 1

# 911 Running an SQL Statement with the EXTRACT Function on NULL Date Columns Causes Error in Delphi 6

The following SQL is intended to create a list of all those with birthdays in September and October. It works as expected when I run it from DBSYS, but fails when I run it from my program using the same data. It raises an EConvertError "0.0 is not a valid timestamp".

SELECT M.FULLNAME, M.BIRTHDATE, 
              EXTRACT(MONTH FROM M.BIRTHDATE) AS MM, 
              EXTRACT(DAY FROM M.BIRTHDATE) AS DD
FROM CAPeople M
WHERE (M.BIRTHDATE IS NOT NULL) AND
      ((EXTRACT(MONTH FROM M.BIRTHDATE) > 9) OR
      ((EXTRACT(MONTH FROM M.BIRTHDATE) = 9)  AND
      (EXTRACT(DAY FROM M.BIRTHDATE) > 1)))  AND
      ((EXTRACT(MONTH FROM M.BIRTHDATE) < 10) OR
      ((EXTRACT(MONTH FROM M.BIRTHDATE) = 10) AND
      (EXTRACT(DAY FROM M.BIRTHDATE) < 31)))
ORDER BY MM, DD

Comments

This problem was caused by the fact that Delphi 6 does not handle NULL dates the same when converting them to timestamps internally.

Resolution

Fixed Problem on 10/1/2001 in version 2.12 build 1

More Support Options

Search Incident Reports

Support Forums

Frequently Asked Questions

Technical Bulletins

Technical Articles

Product Manuals

Roadmap