Oracle use LIKE '%' on DATE

sql oracle date-comparison
Mihawk picture Mihawk · Oct 15, 2015 · Viewed 48.7k times · Source

My table myTab has the column startDate, which has the datatype "DATE". The data in this column are stored like dd.mm.yyyy.

Now I'm trying to get data with this query:

SELECT * FROM myTab WHERE startDate like '%01.2015"

Somehow it doesn't work and I don't know why.

Hope someone can help.

Answer

Guffa picture Guffa · Oct 15, 2015

To make a text search on the date you would have to convert the date to text.

It's more efficient if you calculate the first and last date for what you want to find and get everything between them. That way it's done as numeric comparisons instead of a text pattern match, and it can make use of an index if there is one:

SELECT * FROM myTab WHERE startDate >= DATE '2015-01-01' AND startDate < DATE '2015-02-01'

Related questions

Comparing Dates in Oracle SQL

I'm trying to get it to display the number of employees that are hired after June 20, 1994, Select employee_id, count(*) From Employee Where to_char(employee_date_hired, 'DD-MON-YY') > 31-DEC-95; But I get an error saying "JUN' invalid identifier. …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More