Incompatibility with DATE type in Paradox and Crea

I recently tried to create a new table using Corel Paradox 10. I chose INTERBASE (using the free Interbase 6 download)for the table type. One table was easily created, but a second table, where I chose DATE for the date field created an error, telling me that Interbase was using TIMESTAMP. Unfortunately, TIMESTAMP is not a choice on the field type dropdown dialogue. Going the otherway and using Interbase SQL, I could create a table with a DATE datetype, but Paradox wouldn't let me add it to the datamodel on a form. Any ideas how to get around this problem?
Would I still have the problem with the Corel Linux version and Interbase for FreeBSD/Linux? (I have Corel Office for Linux on order).

We recently encountered this problem in a Paradox-to-InterBase conversion project, and found the problem to be related to the SQL dialect used by the BDE driver.



InterBase 6 supports dialect 1 - where only the TIMESTAMP field type is supported, and dialect 3 - where field types DATE and TIME have been added.



In order to get Paradox to use the dialect 3 in InterBase 6, you will need to add a key to the registry settings for the driver. In regedit, you will need to add a key under:

HKEY_LOCAL_MACHINE\SOFTWARE\Borland\Database Engine\Settings\Drivers\INTRBASE\DB OPEN\

with the following settings:

Key = SQLDIALECT
Value = "3"



You will then need to delete any existing aliases to InterBase 6 databases in the BDE Administrator (Paradox Alias Manager), and re-create them.



Once you have done this, the DATE field type in Paradox will match the DATE field type in InterBase.



One very important note:
While Paradox dates allow such things as 3 and 5 digit years - InterBase dates only support values between November 18, 1858 and January 1, 3000! I mention this, because it is possible to get a "data overflow" error on inserts from Paradox to InterBase where funky date values exist.