DSXchange

Hi folks,
I am getting data for a date field in different formats like - DD-MM-YY,
DD-MON-YY, MM-DD-YYYY etc as the date field is varchar in source system. Now I need to format the dates in structure and load.

please suggest if you have any idea..

First convert your multi-format date to standard MM-DD-YYYY format in 3 stage variables.

If your incoming year part 2 bytes then standardize to 4 bytes year part.

If month part is like JAN / FEB then standardize to 2 bytes numerical part.


DD-MM-YY = 09-11-08 (standardize to 11-10-2008)
DD-MON-YY = 09-NOV-08 (standardize to 11-10-2008)
MM-DD-YYYY = 11-09-2008 this format doesn't need conversion

Use StringToDate(%datestring%,"%mm-%dd""%yyyy") to covert your standardized date