Convert Varchar into Date

AshleyB · #1 (**permalink**) 06-20-06, 09:07

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

Tank · #2 (**permalink**) 06-21-06, 23:07

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

Peter.Vanroose · #3 (**permalink**) 06-24-06, 08:50

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.

grofaty · #4 (**permalink**) 06-26-06, 02:01

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