How to convert string to date format

Analytics
Enthusiast

How to convert string to date format

I want to convert below string date format from

'm/d/yy'

'mm/d/yy'

'm/dd/yy'

'mm/dd/yy'

'm/d/yyyy'

'mm/d/yyyy'

'm/dd/yyyy'

'mm/dd/yyyy'

to

'mm/dd/yyyy'

 

Below example is working fine for - format with YYYY for any D/DD and/or M/MM case:

 

SEL cast(LPAD(REGEXP_REPLACE('1-3-2017','(-)(?=[0-9](-|$))','\10',1,0,'c'),10,'0') as date format 'MM-DD-YYYY');

But not working for YY case

SEL cast(LPAD(REGEXP_REPLACE('1-3-17','(-)(?=[0-9](-|$))','\10',1,0,'c'),10,'0') as date format 'MM-DD-YYYY');

 Any help..


Accepted Solutions
Enthusiast

Re: How to convert string to date format

This is exacting what I want & working fine, thank you!!!

1 ACCEPTED SOLUTION
3 REPLIES
Enthusiast

Re: How to convert string to date format

also if STRING date format is YY and >50 then I wan to make 20YY else 19YY..

Apprentice

Re: How to convert string to date format

Hi,

Try the following.

CREATE SET VOLATILE TABLE vt1
(col1 INTEGER
,col2 VARCHAR(10)
)
ON COMMIT PRESERVE ROWS;

DELETE FROM vt1;
INSERT INTO vt1 VALUES(1,'5/1/17');
INSERT INTO vt1 VALUES(2,'05/1/17');
INSERT INTO vt1 VALUES(3,'5/01/17');
INSERT INTO vt1 VALUES(4,'05/01/17');
INSERT INTO vt1 VALUES(5,'5/1/2017');
INSERT INTO vt1 VALUES(6,'05/1/2017');
INSERT INTO vt1 VALUES(7,'5/01/2017');
INSERT INTO vt1 VALUES(8,'05/01/2017');
INSERT INTO vt1 VALUES(9,'05/01/51');

SELECT a.*
  ,CASE
   WHEN CHARS(STRTOK(col2,'/',3)) = 4 OR (CHARS(STRTOK(col2,'/',3)) = 2 AND STRTOK(col2,'/',3) > '50') THEN
     CAST((RIGHT('0'||STRTOK(col2,'/',1),2)||'-'||RIGHT('0'||STRTOK(col2,'/',2),2)
          ||'-'||RIGHT('20'||STRTOK(col2,'/',3),4)) AS DATE FORMAT 'mm-dd-yyyy') 
   ELSE
     CAST((RIGHT('0'||STRTOK(col2,'/',1),2)||'-'||RIGHT('0'||STRTOK(col2,'/',2),2)
          ||'-'||RIGHT('19'||STRTOK(col2,'/',3),4)) AS DATE FORMAT 'mm-dd-yyyy') 
   END AS dt1
FROM vt1 AS a
ORDER BY 1;

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: How to convert string to date format

This is exacting what I want & working fine, thank you!!!