Query:
SELECT ps.nr, ps.job_function_title, p.pid, p.name_last, p.name_first, p.date_birth, p.sex FROM care_personell AS ps, care_person AS p WHERE (ps.nr LIKE '%07/17/1984%' OR ps.job_function_title LIKE '%07/17/1984%'
Or p.pid LIKE '%07/17/1984%' OR p.name_last LIKE '%07/17/1984%'
OR p.name_first LIKE '%07/17/1984%'
OR p.date_birth LIKE '%07/17/1984%')
AND p.pid=ps.pid AND ps.job_type_nr=2 ORDER BY p.name_last ASC

problem: if p.date_birth LIKE '%07/17/1984%' , it will not return value.
What is the format for LIKE operator for a Date format in db2?

LIKE predicate works for string expression.

So, you should convert date data type to char(or varchar) data type to apply LIKE predicate.

If CHAR builtin function was used to convert from date data type to char data type,
you can specify second parameter to specify the format.

For example:
CHAR(current_date , ISO) will return '2011-05-11'
CHAR(current_date , USA) will return '05/11/2011'

i tried p.date_birth LIKE CHAR('07/17/1984') but still it returns no value

WHY? DB2 is a database, no steeplechase contender. How can a date be "like" july 17th 1984?

how can I cast date to string in db2?

I felt that adodb21 didn't read my descriptions.

it's not working actually...
it says
The data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.CHAR" is incorrect. Parameter name: "".. SQLCODE=-171, SQLSTATE=42815, DRIVER=4.11.69

What is a data type of p.date_birth?

In other words, please show the result of

DESCRIBE SELECT ps.nr, ps.job_function_title, p.pid, p.name_last, p.name_first, p.date_birth, p.sex FROM care_personell AS ps, care_person AS p WHERE (ps.nr LIKE '%07/17/1984%' OR ps.job_function_title LIKE '%07/17/1984%'
Or p.pid LIKE '%07/17/1984%' OR p.name_last LIKE '%07/17/1984%'
OR p.name_first LIKE '%07/17/1984%'
OR p.date_birth LIKE '%07/17/1984%')
AND p.pid=ps.pid AND ps.job_type_nr=2 ORDER BY p.name_last ASC;

date data type
the result should display all entries with birthdate: 07/17/1984

I tried casting:
SELECT ps.nr, ps.job_function_title, p.pid, p.name_last, p.name_first, p.date_birth, p.sex FROM care_personell AS ps, care_person AS p WHERE (ps.nr LIKE '%07/17/1984%' OR ps.job_function_title LIKE '%07/17/1984%'
Or p.pid LIKE '%07/17/1984%' OR p.name_last LIKE '%07/17/1984%'
OR p.name_first LIKE '%07/17/1984%'
OR CAST(p.date_birth as varchar(15)) LIKE '%07/17/1984%' )
AND p.pid=ps.pid AND ps.job_type_nr=2 ORDER BY p.name_last ASC

but I've got same result

Especially this descriptions.

i tried

SELECT char(bday,ISO)
FROM person where bday like '%10/26/1984%'

but it doesnt return anything. why is this so tonkuma?
or did i get what you mean?

I can't understand why you couldn't reach a simple answer.

I want to wait until you find the answer by yourself, rather than I give you it.

I think that you already recieved enough hints.

hahaha! yeah i got it where it goes wrong, i should have converted it into char before the LIKE, guess i also didnt pay attention to tonkuma-san

Why on earth would you want to do that?

There won't be any other result if you simply compare for equality using

p.date_birth = '07/17/1984'

should return the same result and will be a lot more efficient if date_birth is an indexed column.

(not sure about the input format for the date literal though, might depend on your locale settings)