How to take floor of date?(s26)

I've got a table with a DATE datatype column in it.  I want to select all the rows which contain dates which are the same calendar day as another date I've got.  What's the best way to do that?

One way I can see to do that would be to take the integer part of my selection date, which would give me time 00:00:00 of that date, then add 1 to it, giving me 00:00:00 of the next date, then doing:

select * from table where date >= midnight_today and date < midnight_tomorrow

but I don't see any way to do the "take the integer part" bit.

The only way I can see to do this is to use the to_char function, converting just the year, month, and day-of-month, then use to_date to convert that back to a DATE datatype, but that's pretty gross.


Ans1:

Use TRUNC function.... it will remove the time out of the date (putting it at midnight)...
Like :

where tabl.date between trunc(parm_date) and trunc(parm_date) + 1

DO NOT TRUNC the date fields referencing your table, your select will not use indexes if u do.

where trunc(tabl.date) between parm_date and parm_date + 1


Ans2:

How about this:

    SELECT * FROM TABLE
    WHERE ROUND(date_column, 'DAY') = ROUND(another_date, 'DAY');

-- Greg Johnson


Ans3:

TRUNC(sysdate) will do what you desire.  This is a default behavior, you can place (a limited set) a format to control where the date is truncated.
 
 

Hosted by www.Geocities.ws

1