Oracle use LIKE '%' on DATE

Question

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 1

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'

Answer 2

SELECT * FROM myTab WHERE TO_CHAR(startDate,'dd.mm.yyyy') LIKE '%01.2015'