Re: sysdate without time

Andy Hassall wrote:
> On Thu, 27 Jul 2006 17:21:33 -0400, Bertie Brink <bhb_at_donotspammailme.com>
> wrote:
>
> >What is the best way to assign sysdate to a column in a trigger with
> >time component set 00:00:00?
> >
> >Also how can one compare a column to sysdate in a query, while ignoring
> >the time components in both?
>
> TRUNC
>
> --
> Andy Hassall :: andy@andyh.co.uk :: http://www.andyh.co.uk
> http://www.andyhsoftware.co.uk/space :: disk and FTP usage analysis tool

Bertie, Andy is right just trunc(sysdate) to ignore the time protion. Since the data is stored in the table with the time portion set to midnight then by keeping the function to the right of the relational operator:

table.column = trunc(sysdate)

This allows the CBO to use an index on the date column if it makes sense to do so. If the data in the column has the time portion stored then code like this:

table.column >= trunc(sysdate) and table.column < ( trunc(sysdate) + 1 )

This will make all the times for one day fall in the desired range of 00:00:00 to 23:59:59 while allowing possible use of the index, if any.

HTH -- Mark D Powell -- Received on Thu Jul 27 2006 - 19:16:34 CDT