Results 1 to 14 of 14
  1. #1
    Join Date
    May 2011
    Posts
    12

    Unanswered: Like operator for date in db2

    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?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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'

  3. #3
    Join Date
    May 2011
    Posts
    12
    i tried p.date_birth LIKE CHAR('07/17/1984') but still it returns no value

  4. #4
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    WHY? DB2 is a database, no steeplechase contender. How can a date be "like" july 17th 1984?

  5. #5
    Join Date
    May 2011
    Posts
    12
    how can I cast date to string in db2?

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt that adodb21 didn't read my descriptions.

  7. #7
    Join Date
    May 2011
    Posts
    12
    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

  8. #8
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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;

  9. #9
    Join Date
    May 2011
    Posts
    12
    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

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I felt that adodb21 didn't read my descriptions.
    Especially this descriptions.
    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'
    Last edited by tonkuma; 05-11-11 at 04:11. Reason: Replaced all descriptions in this article.

  11. #11
    Join Date
    May 2011
    Posts
    5
    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?

  12. #12
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  13. #13
    Join Date
    May 2011
    Posts
    5
    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

  14. #14
    Join Date
    Nov 2003
    Posts
    2,935
    Provided Answers: 12
    Quote Originally Posted by adodb21 View Post
    problem: if p.date_birth LIKE '%07/17/1984%' , it will not return value.
    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)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •