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.

Gentes

If you want all rows in January 2015 use where to_char(startDate ,'Mon-YYYY')='Jan-2015'; – Chuppah

Have a look at Oracle SQL comparison of DATEs returns wrong result – Morton

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'

Civility

I would appreciate if you also mention that WHY using a date range condition is better here. Basically, it is about performance, i.e. usage of a B*Tree index(if any). – Morton

"That way it's a plain numeric comparison instead of a text pattern match" No, that's not true, it is not at all a numeric comparison. In Oracle, DATE and NUMBER are completely different.' – Morton

I appreciate it. But, please replace "numeric comparison" with "date comparison" as it is more appropriate. Date is different from numeric(number in oracle). – Morton

@LalitKumarB: A date is stored as a set of numerical components, so comparing dates is done by doing numeric comparisons of the corresponding numerical components. I can't see how "numeric comparisons" would not be appropriate. – Civility

Please provide the link to the official Oracle documentation where you have read that. Oracle stores date in internal format in 7 bytes. And internal format is no way "numeric". So, just saying "date in oracle is compared as numeric" is baseless. By the way, I have seen few SQL Server developers saying such things. Is it the same with you? – Morton

@LalitKumarB: I wonder what you think that bytes are? When the database compares those bytes values, do you think that it uses any other way than doing a numeric comparison? – Civility

Lalit is correct, date comparisons are handled differently to numeric comparisons. However, I do get what you are trying to say Guffa - effectively the comparison is numeric in nature and it is useful to think of them that way (i.e. each date value represents a particular point in time to the nearest second, and can be thought of as the number of seconds past a particular epoch - which is why we can compare dates, "subtract" one from another, and add numeric values to them). – Indiaindiaman

@JeffreyKemp: Naturally date comparisons are handled differently than comparing the Number data type, but I never said that dates are compared as Number values, I said that the comparison is numeric. – Civility

Perhaps we all are saying the same thing, but interpreting differently. I guess we helped OP with what he wanted. – Morton

Answer 2

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

Washerman

Your way kinda worked. Now I've got the Problem that other Dates doesn't work. If I try to use '%01.2013' i don't get any data but I know there are Dates in this range... – Gentes

@Lalit, can I humbly suggest you relax your tone a bit? This answer is not "completely wrong", it's just not the best. In fact, this answer will work, although there are much much better ways of solving the problem, and does not, in fact, rely on implicit data type conversion. – Indiaindiaman

@Jeffrey, fair enough :-) – Morton

Answer 3

If the field type is "DATE" then the value isn't stored as a string, it's a number managed by Oracle, so you have 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');

Pagas

Please read first about implicit data conversions:here docs.oracle.com/cd/E11882_01/server.112/e41084/… Oracle automatically converts a date to varchar in this case, your answer is not precise.. – Dialyze

to_char() is explicit: docs.oracle.com/cd/E11882_01/server.112/e41084/… – Pagas

Answer 4

Regarding you actual question "Somehow it doesn't work and I don't know why."

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

Tannic

Answer 5

The data in this column are stored like dd.mm.yyyy.

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

WHERE startDate like '%01.2015"

You are comparing a DATE with a STRING, which is pointless.

From performance point of view, you should use a date range condition so that if there is any regular INDEX on the date column, it would be used.

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

To understand why a Date range condition is better in terms of performance, have a look at my answer here.

Morton

Answer 6

I solved my problem that way. Thank you for suggestions for improvements. 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 + "%'";

Esch

Answer 7

To provide a more detailed answer and address this https://mcmap.net/q/1043980/-oracle-use-like-39-39-on-date answer's issue.

In Oracle a column of type "date" is not a number nor a string, it's a "datetime" value with year, month, day, hour, minute and seconds. The default time is always midnight "00:00:00"

The query:

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

won't work in all circumstances because a date column is not a string, using "like" forces Oracle to do a conversion from date value to string value. The string value may be year-month-day-time or month-day-year-time or day-month-year-time, that all depends how a particular Oracle instance has set the parameter NLS_DATE_FORMAT to show dates as strings.

The right way to cover all the possible times in a day is:

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')

Accouchement

The query is a string built inside C# so the start quote and last quote are needed – Accouchement

Answer 8

SELECT * FROM myTab WHERE startDate like '%-%-2015';

This will search for all dates in 2015. If this doesn't work, try:

SELECT * FROM myTab WHERE startDate like '%-%-15';

Cilka