Convert varchar to date data type

Database
Enthusiast

Convert varchar to date data type

Hi Team, 

i have a requirement, that i need to change the data  type from varchar to date data type, if there is any invalid data i need to make it to null and send it to target and if it is valid data move directly to target. 

Sample Source Data(dd/mm/yyyy)

-------------------

1

2/2/11

00/12/12

21/2/15

01/4%/00

Target

-----------

NULL

02/02/2011

NULL

21/02/2015

NULL

4 REPLIES
Junior Supporter

Re: Convert varchar to date data type

Three words: LEFT OUTER JOIN (with Calendar table).

HTH

Cheers.

Carlos.
Enthusiast

Re: Convert varchar to date data type

Thanks For your revert Carlos, apart from this, is there is any way...

Junior Supporter

Re: Convert varchar to date data type

Hi.

You could write a SQL UDF that can parse the varchars into dates/nulls using regexp functions, for example.

Cheers.

Carlos.
Enthusiast

Re: Convert varchar to date data type

Create volatile multiset table Temp1
(Id Int,
DTE Varchar(10)
) on commit preserve rows;

Insert into Temp1 (1, '1' );
Insert into Temp1 (2, '2/2/11');
Insert into Temp1 (3, '00/12/12');
Insert into Temp1(4,'21/2/15');
Insert into Temp1(5, '01/4%/00');

Sel
Id,
CAST(LPAD(STRTOK(DTE,'/',1),2,'0')  as CHAR(2)) as DT,
CAST(LPAD(STRTOK(DTE,'/',2),2,'0')  as CHAR(2)) as MTH,
CAST(LPAD(STRTOK(DTE,'/',3),2,'0')  as CHAR(2)) as YY,
CASE WHEN CAST(LPAD(STRTOK(DTE,'/',3),4,'0')  as CHAR(2)) like '00%' THEN '20' ELSE
CAST(LPAD(STRTOK(DTE,'/',3),4,'0')  as CHAR(2))
END as CC,
DT||'/'||MTH||'/'||CC||YY as DTE1,
CASE WHEN ((CC||YY between 1000 and 9999) and (MTH > '01' and MTH <'13') and (DT Between 1 and 31)) THEN
DTE1
ELSE Null
End as Op
from Temp1
Order by 1

This could be one alternative... its using only string operations. I have tried to be very detailed in the query.

 HTH.

-Ranga