Date function

General
Highlighted
Enthusiast

Date function

Hi,

 

I have an table with date column as varchar. It will have values as below:

 

yyyy-mm-dd - eg: 2017-05-01

dd/mm/yy - eg:04/08/17

d/m/yy- eg: 4/8/17

d/m/yyyy - eg:4/8/2017

dd/mm/yyyy -eg:04/08/2017

 

Now I have to covert all these to a proper format ie., YYYY-MM-DD and then cast it to varchar.

 

CAST(cast(date_values as date format 'YYYY-MM-DD') AS VARCHAR(50) doesnot work. Throws, invalid date supplied error.

 

Strtok and then concatenation, I could think, but makes it complicated, I feel. Any other options please! 

 

1 REPLY 1
Teradata Employee

Re: Date function

I got nothing better than a strtok too (or instr / or regexp_substr...)

 

create multiset volatile table mvt_dates, no log
( pi_mvt        byteint
, dt_varchar    varchar(10)
)
primary index (pi_mvt)
on commit preserve rows
;

insert into mvt_dates (pi_mvt, dt_varchar) values (122, '2017-05-01');
insert into mvt_dates (pi_mvt, dt_varchar) values (122, '04/08/17'  );
insert into mvt_dates (pi_mvt, dt_varchar) values (122, '4/9/17'    );
insert into mvt_dates (pi_mvt, dt_varchar) values (122, '5/9/2017'  );
insert into mvt_dates (pi_mvt, dt_varchar) values (122, '04/08/2017');

select dt_varchar
     , case
         when instr(dt_varchar, '-') > 0 then dt_varchar
         when instr(dt_varchar, '/') > 0 then cast(right('20' || strtok(dt_varchar, '/', 3), 4) || '-'
                                                || right( '0' || strtok(dt_varchar, '/', 2), 2) || '-'
                                                || right( '0' || strtok(dt_varchar, '/', 1), 2) as varchar(10))
       end as dt_modified
     , cast(dt_modified as date format 'yyyy-mm-dd')
  from mvt_dates
 where pi_mvt = 122;

Posting to see future answers !