Date search wildcard

Question

I am trying to find a DB for Date based records. But the search is based on a month and a year. ie mm/yyyy and dd should be wild-card.

My search query looks like this:

 Select ucid, uc_name, From (UC_Table1) where UC_Date like To_Date('11/*/2011','mm/dd/yyyy') 

this gives me the following error: ORA-01858: a non-numeric character was found where a numeric number was expected. So obviously he doesn't like * or % or _ or ? like wild-cards for dd.

Answer 1

Symbols do not work as they do in a function. The To_Date() function parses * before LIKE can see it. Consider:

 SELECT ucid, uc_name FROM UC_Table1 WHERE UC_Date >= To_Date('11/01/2011', 'mm/dd/yyyy') AND UC_Date < To_Date('12/01/2011', 'mm/dd/yyyy')