Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    1

    Unanswered: Convert Varchar into Date

    Hi,

    I have a column "START_DT" which is of type VARCHAR. This column is having date data in this format Dec 07 2005 10:35:51 (MMM DD YYYY 00:00:00).

    I have to do some search on the table "AFFILIATE_TB" based on from and to date from this START_DT column. Say I want to search all records between date 1st Oct 2005 to 1st Oct 2006.

    How I can make this query in DB2?

    Thanks very much for the help!
    -Ashley

  2. #2
    Join Date
    Feb 2004
    Location
    Copenhagen
    Posts
    220

    Arrow substr / cast

    Do a substring of your attribute and concatenate them to the format 'yyyy-mm-dd', cast them to date and then search

    - sorry you have to translate month first , use a table

    - there might be a smarter solution but the american users (non ISO :-() will have to tell you

    - i think there is a function for converting the (lame) US date format to ISO please search the IBM site

    - ISO upholds an american date standard as well but its purely cosmetical
    Last edited by Tank; 06-22-06 at 01:28.
    Kristian K. Hansen
    Project Supervisor
    National Board of Health

  3. #3
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Use the conversion
    Code:
    CAST (substr(start_dt,8,4) ||
          CASE substr(start_dt,1,3)
            WHEN 'Jan' THEN '01'
            (etc.)
            WHEN 'Dec' THEN '12'
          END || substr(start_dt,5,2) AS DATE)
    You can query your original table, but now referring to START_DT as a real DATE field, by placing the following subquery in the FROM clause of your query:
    Code:
    (SELECT col1, col2, *** AS start_dt, col4 FROM AFFILIATE_TB) AS new_tb
    where you plug in the above CAST() in the place of the "***", and where I have assumed that AFFILIATE_TB has columns called col1, col2, start_dt, col4 in that order.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  4. #4
    Join Date
    Jan 2003
    Posts
    1,605
    Hi,
    the above query returs error:
    SQL0180N The syntax of the string representation of a datetime value is
    incorrect. SQLSTATE=22007

    You need to write a '-' character to present date. Try this one:
    select CAST (substr(start_dt,8,4) || '-' || CASE substr(start_dt,1,3) WHEN 'Jan' THEN '01' WHEN 'Feb' THEN '01' WHEN 'Mar' THEN '03' WHEN 'Apr' THEN '04' WHEN 'May' THEN '05' WHEN 'Jun' THEN '06' WHEN 'Jul' THEN '07' WHEN 'Aug' THEN '08' WHEN 'Sep' THEN '09' WHEN 'Oct' THEN '10' WHEN 'Nov' THEN '11' WHEN 'Dec' THEN '12' END || '-' || substr(start_dt,5,2) AS DATE) from your_table

    Hope this helps,
    Grofaty

  5. #5
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by grofaty
    You need to write a '-' character to present date.
    Of course - sorry for this mistake. Thanks for correcting.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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