I am reading a flatfile on which few columns are of the type date.
Those columns contains valid date format for most of the rows : mm-dd-yyyy hh:nn:ss
However some of the rows contain various string to show that the information is not available - example "NA" - "[Not Available]" - "[Access Denied]" and so on...
I am using an derived column component to typecase the date formatted string into DT_DBTIMESTAMP and want to reject the records which is not of the form -- that is arbitary strings.
I am using the logic :
Check if the column contains ":" if yes - then it seems to be a valid date of the format mm-dd-yyyy hh:nn:ss -- else consider this to be a NULL date.
LEN([Password Last Set Time]) == 0 ? NULL(DT_DBTIMESTAMP) : ((FINDSTRING([Password Last Set Time],":",1) == 0) ? NULL(DT_DBTIMESTAMP) : (DT_DBTIMESTAMP)([Password Last Set Time]))
Please let me know if you feel this is the correct approach - or advise accordingly.
Your help is much appreciated.