Oracle uses LIKE '%' on DATE

Question

My myTab table has a startDate column that has a DATE data type. The data in this column is stored as dd.mm.yyyy .

Now I am trying to get data with this query:

 SELECT * FROM myTab WHERE startDate like '%01.2015" 

Somehow this does not work, and I do not know why.

Hope someone can help.

Answer 1

To do a text search on a date, you will need to convert the date to text.

This is more effective if you count the first and last date of what you want to find and get everything in between. Thus, this is done as numerical comparisons instead of matching a text template, and it can use the index, if any:

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

Answer 2

If the field type is "DATE", then the value is not saved as a string, it is a number controlled by Oracle, so you need to convert it to a string:

 SELECT * FROM myTab WHERE to_char(startDate, 'MM.YYYY') = '01.2015'; 

You can also use date ranges in SQL queries:

 SELECT * FROM myTab WHERE startDate BETWEEN to_date('01.01.2015', 'DD.MM.YYYY') AND to_date('31.01.2015', 'DD.MM.YYYY'); 

Answer 3

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

Answer 4

Regarding your actual question: "Somehow this does not work, and I do not know why."

Oracle does an implicit conversion from DATE to VARHCAR2 , however it uses NLS_DATE_FORMAT by default, which is probably different from what you use in your query.

Answer 5

The data in this column is stored as dd.mm.yyyy.

Oracle does not save the date in the format you see. It stores it internally in a proprietary format of 7 bytes with each byte storing different components of the datetime value.

WHERE startDate like '% 01.2015 "

You are comparing DATE with STRING , which is pointless.

In terms of performance you must use the date range condition so that if there is a regular INDEX in the date column it will be used.

 SELECT * FROM table_name WHERE date_column BETWEEN DATE '2015-01-01' AND DATE '2015-02-01' 

To understand why the date range condition is better in terms of performance , see my answer here .

Answer 6

I solved my problem this way. Thank you for suggestions for improvement. An example in C #.

 string dd, mm, aa, trc, data; dd = nData.Text.Substring(0, 2); mm = nData.Text.Substring(3, 2); aa = nData.Text.Substring(6, 4); trc = "-"; data = aa + trc + mm + trc + dd; "Select * From bdPedidos Where Data Like '%" + data + "%'"; 

Answer 7

To provide a more detailed answer and answer this question fooobar.com/questions/1233799 / ....

In Oracle, a “date” column is not a number or a string, but a “datetime” value with a year, month, day, hour, minute, and second. The default time is midnight "00:00:00"

Request:

 Select * From bdPedidos Where Data Like '%" + data + "%'" 

will not work under any circumstances because the date column is not a string, using a "how" to force Oracle to convert from a date value to a string value. The string value can be year-month-day-time or month-day-year-year or day-month-year-time, it all depends on how a particular Oracle instance set the NLS_DATE_FORMAT parameter to display dates as strings.

The right way to cover every possible moment of the day:

 Select * From bdPedidos Where Data between to_date('" + data + " 00:00:00','yyyy-mm-dd hh24:mi:ss') and to_date('" + data + " 23:59:59','yyyy-mm-dd hh24:mi:ss')