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..
Convert Multiple date formats in to one
Moderators: chulett, rschirm, roy
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
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
Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives.
By William A.Foster
By William A.Foster