WOW... that was a lot of info... thank you very much for the advice too...


"Helen Borrie" <helebor@...> wrote in message
news:4.2.0.58.20011114214900.02330100@......

> 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
> _______________________________________________________
>
>
>

___________________________________________________________________________

> IB Objects - direct, complete, custom connectivity to Firebird or

InterBase

> without the need for BDE, ODBC or any other layer.
>

___________________________________________________________________________

> http://www.ibobjects.com - your IBO community resource for Tech Info

papers,

> keyword-searchable FAQ, community code contributions and more !
>
> Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
>
>
>