sql NOT LIKE syntax
Solution 1
You should be able to just cast to a date and compare to your desired date, something like;
SELECT *
FROM P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM
WHERE service_type_id = 134469888
AND CAST(valid_from_tsp AS DATE) = '2014-02-06'
EDIT: If you have a large table, this query will not use indexes well. If that is important, just do a range check between midnight and next midnight instead;
SELECT *
FROM P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM
WHERE service_type_id = 134469888
AND valid_from_tsp >= '2014-02-06' AND valid_from_tsp < '2014-02-07'
Solution 2
The following is a better way to express the date condition:
select *
from P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM
where service_type_id = 134469888 and
valid_from_tsp >= DATE '2014-02-06' and
valid_from_tsp < DATE '2014-02-07';
or:
select *
from P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM
where service_type_id = 134469888 and
valid_from_tsp >= DATE '2014-02-06' and
valid_from_tsp < DATE '2014-02-06' + interval '1' day;
The difference is important. In general, when you have a function on a column, the database does not use indexes. So these forms will use an index on the column. The best index for this query is T_CLAIM_SERVICE_TYP_DIM(service_type_id, valid_from_tsp)
.
staples
Updated on June 08, 2022Comments
-
staples almost 2 years
I need to filter based on a timestamp and would like to get everything within a certain day. The timestamps are like this:
02/06/2014 7:45:59 AM
or translated2014-02-06 07:45:59
select * from P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM where service_type_id = 134469888 and valid_from_tsp not like '2014-02-06 %'
When I run this query, I am returned the error:
Partial String matching requires character operands
Upon searching this error, I was given
The user used the partial string matching operator (LIKE) with an argument that was not a character string.
So what can I use to match a date? edit: or in this case, not a date?