Re: Date and DateTime columns

Maybe 'use of indexes' needs some elaboration: if you keep the time fraction in your column and you want to use the date fraction in a query you have to code something like

        trunc(date_column) = ...

or specify a BETWEEN clause with time fraction 00:00:00 and 23:59:59 (not having to specify this in every SQL-statement is probably what Dag Arne means by 'cleaner SQL') .

In the first case, if there's an index on date_column, Oracle won't/can't use this index because of the trunc function.

So I agree with Dag Arne it's good practice to keep the time fraction out of a date column that is only intended to contain the date.

Jaap.

On Wed, 9 Jan 2002 10:00:42 +0100, "Dag Arne Matre" <dag-arne_at_matreweb.com.antispam> wrote:

>> How do others handle this situation?
>>
>
>We go with "the other fraction" (two domains in our modelling tool):
>CREATE TABLE EXAMPLE (
> XXX_TS DATE,
> XXX_DT DATE CHECK (XXX_DT = TRUNC(XXX_DT))
>)
>
>The main reason for this is that _our_ definition of "Date Only"
>says it should not contain any time fraction.
>Other reasons for this includes cleaner SQL, use of indexes, etc.
>
>My $.02...
>
>
Received on Wed Jan 09 2002 - 03:57:06 CST