title: How do I format dd-mmm-yy values in flat file to smalldatetime during data import?
body: <p>I have a flat file which is imported into SQL Server via an existing SSIS package. I need to make a change to the package to accommodate a new field in the flat file. The new field is a date field which is in the format <code>dd-mmm-yy</code> (e.g. <em>25-AUG-11</em>). The date field in the flat file will either be empty (e.g. a space/whitespace) or populated with a date. I don’t have any control over the date format in the flat file.</p>
  
  <p>I need to import the date field in the flat file into an existing SQL Server table and the target field data type is smalldatetime.</p>
  
  <p>I was proposing to import the date as a string into a load table and then convert to smalldatetime when taking the data from the load table. But is there another possible way to parse the date format  dd-mmm-yy to load this straight into a smalldatetime field without having to use convert to smalldatetime from the load table. I can’t quite think how to parse the date format, particularly the month. Any suggestions welcome.</p>