How do I format dd-mmm-yy values in flat file to smalldatetime during data import?

Question

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 dd-mmm-yy (e.g. 25-AUG-11). 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.

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.

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.

Solution

It should load straight into a SMALLDATETIME field as it is. Remember, dates are just numbers in SQL Server, which are presented to the user in the desired date/time format. The SSIS package should read 25-AUG-2011 just fine as a date data type, and insert it into a SMALLDATETIME field without issues.

Was the package throwing an error or something?

Comments

[Adrian S]

The package was throwing a conversion error but somone has told me this could be because of the records which are space/whitespace so I need to do a check for them and insert a NULL(DT_DATE).