Toad World® Forums

Trying to use a date in the data filter


#1

I have tried using a date field in the data filter. I want all the data for 12/18/2009 but I keep getting a format error


#2

You are entering a string instead of a date. Date are dates and strings are
strings and you just tried to compare a date to a string this is why you are
getting the formatting error.

Try entering to_date(‘12/18/2009’,‘mm/dd/yyyy’) into the filter for your date
field.

If your date field has the time portion set then you have 2 options

BETWEEN TO_DATE( ‘12/18/2008 00:00:00’, ‘mm/dd/yyyy hh24:mi:ss’ )
AND TO_DATE( ‘12/18/2008 23:59:59’, ‘mm/dd/yyyy hh24:mi:ss’ )

or

TRUNC(date_column) = to_date(‘12/18/2009’,‘mm/dd/yyyy’)

But be aware that using TRUNC on your column will cause the optimizer to not use
the index if you have one on that column.

Ed

[TeamT]