Re: timestamp/date comparison

On Fri, 13 Feb 2004, Campano, Troy wrote:

> Hi,
> I'm trying to compare a timestamp to current_timestamp but I'm having
> trouble.
> I want to compare just the date piece of my timestamp column to just the
> date piece of current_timestamp.
>
> I'm getting weird results that I don't understand.
> When I use TO_DATE it changes the year, month, etc.

I think you probably don't want to_date in any case. CAST(whatever AS
DATE) is probably better. The to_date way probably is taking the
timestamp converting it to text and then attempting to convert the text
back.

> anna=> SELECT request_date,TO_DATE(request_date,'YYYY-MM-DD HH24:MM:SS')

Here, you're using the minutes as month information I think.

> anna=> SELECT
> TO_DATE(request_date,'MM/DD/YYYY'),TO_DATE(current_timestamp,'MM/DD/YYYY
> ') FROM anna_onestop_database_t WHERE TO_DATE(request_date,'MM/DD/YYYY')
> > TO_DATE(current_timestamp,'MM/DD/YYYY');

I'm not sure why this is working at all, but using the standard output
format for a timestamp, it doesn't follow the form MM/DD/YYYY I believe,
so the format string doesn't really line up with the data.