sql NOT LIKE syntax

Question

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 translated 2014-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?

Are valid_from_tsp a timestamp or a string? If it's a timestamp you should use a date function. I don't know teradata, but something like: `cast(valid_from_tsp as date) = '2014-02-06'` — Lennart - Slava Ukraini, May 16 '14 at 13:21

score 3 · Answer 1 · answered May 16 '14 at 13:37

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

Joachim Isaksson · Accepted Answer · 2014-05-16T13:44:33.290

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'

edited May 16 '14 at 13:44

answered May 16 '14 at 13:18

Joachim Isaksson

176,943
25
281
294

@staples Hm, that should work, hard to tell what goes wrong. The rows have id 134469888 and another date? – Joachim Isaksson May 16 '14 at 13:31
1

I checked my data again, I was wrong, and it does work – staples May 16 '14 at 13:33
This would be inefficient in long lists, as the CAST will be applied to all values of valid_from_tsp before processing the logic, negating any indexing. – Matt May 16 '14 at 13:41
@Matt True, added a version that does a range check that can be pre-calculated instead. – Joachim Isaksson May 16 '14 at 13:45

score 0 · Answer 3 · answered May 16 '14 at 13:40

0

use

select * from P_FAR_SBXD.T_CLAIM_SERVICE_TYP_DIM 
where service_type_id = 134469888 and 
not DATEADD(dd, 0, DATEDIFF(dd, 0, valid_from_tsp)) = DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

answered May 16 '14 at 13:40

user3620114

1
1

score 0 · Answer 4 · answered May 16 '14 at 13:48

LIKE operator is for text.

Just a guess (you did not provide table structure), but probably valid_from_tsp is not a varchar column, but a timestamp or date column.

So , instead of

valid_from_tsp not like '2014-02-06 %'

you should use this:

cast(valid_from_tsp as varchar(20)) not like '2014-02-06 %'`

sql NOT LIKE syntax

4 Answers4