At 04:12 AM 14-11-01 -0600, you wrote:

>Hi, I'm having problems accessing a certain table
>It only has 2 fields "FDate" (DATE) and "FValue" (SMALLINT) and for the
>moment is empty (0 records). Primary key on FDate.
>
>If I try to open it with a TIBOTable I get the error "20/Feb/1541 is not a
>valid date and time", if I open it with a TIBOQuery I get no errors...
>
>This doesn't happen if the table does not a primary key on the FDate field.

First, advice about PKs on date or time columns - DON'T!

Reasons: (1) you can't guarantee that every insert will get a unique date or date-and-time value.
(2) Date and time types are stored as doubles in InterBase. High likelihood that you will store "near-matches" in tables with dependent keys. This means you won't retrieve these "supposed-to-be-equal" keys on joins.

Next, the message "20/Feb/1541 is not a valid date and time" is a true statement. In InterBase the formats allowed for date-only literals are 'mm/dd/yyyy', 'dd.mm.yyyy' and 'dd-MMM-yyyy' and their yy variants. In IB6 you can also have ISO format 'yyyymmdd', with no yy variant.

Next (phew), what you get for a DATE type depends on the version and dialect of IB or FB you are using. In FB/IB 6 Dialect 3 DATE is a date-only and the date-and-time type is TIMESTAMP. In FB/ IB 6 Dialect 1 and lower IB versions, DATE stores date and time and it is up to the programmer to make sure that the time portion is stored as midnight if you want to store date-only.

There is more.

In FB/IB 6 you cannot directly submit a date literal to the database in an SQL statement. For example, this is what you do in IB 5.x and lower:

INSERT INTO ATABLE(FDate, FValue)
VALUES('20.02.1541', 199);

But this throws an error in FB/IB 6. You need to cast the literal:
INSERT INTO ATABLE(FDate, FValue)
VALUES(CAST('20.02.1541' AS DATE), 199);

In Dialect 3 that will store the date as the double representation of '20.02.1541'). In Dialect 1 and the sub-6 IB versions, it will store it as the double representation of
'20.02.1541 00:00:00.00'.

So, the advice would be to (a) add a surrogate primary key to your table that uses a generator and (b) get it sorted out just what data type you are dealing with and fix your table up if necessary.

If you are passing literals to SQL in an IBO app(not the best-recommended approach) make sure you are passing a literal or a CAST that your version of IB understands. The ideal when handling date types with IBO is to access them through the Fields[] or Params[] array, as appropriate.

regards,
Helen



All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________