Archives of the TeradataForum

Message Posted: Tue, 16 Oct 2001 @ 17:22:08 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Hi,

The INDEX clause is exactly what you need for this type of situation. Try something like:

select dt
     ,substring('0'||dt from  delim1pos - 1  for 2) as dt_month
     ,substring('0'||substring(dt from delim1pos + 1 for delim2pos-delim1pos-1) from  delim2pos-delim1pos-1 for 2) as dt_day
     ,substring(dt from delim2pos+1 for 4) as dt_year
from (select dt
           ,delim1pos
           ,delim1pos + position('/' in  substring(dt from delim1pos +1)  )
        from (select dt,position('/' in dt)
                 from t1)
               as delim1 (dt, delim1pos))
        as delim2 (dt, delim1pos, delim2pos);

This example works off a table (T1) which contains a column (DT) defined as varchar and containing the sort of data that you described. This code uses two nested derived tables. The first one (DELIM1) finds the position of the first date separator. The second one (DELIM2) finds the position of the second one. The outer query then uses these positions to substring the relevant parts of the date out of the varchar column and add a leading '0' where necessary.

You'll still need to re-combine the date parts before inserting into a date column, but this will give you each part.

You can probably do this without derived tables, which may be necessary if you've got a LOT of data (but there again, if it's coming from Excel presumably you haven't got that much data).

One of the other ideas that was put forward was to control the format of the data supplied to you. That has to be the best answer if possible, but if you can't try this.

Cheers,

Dave

	<Prev	Next>		<<First	<Prev	Next>	Last>>