Thursday, September 24, 2009

SQL Server 2008 Import Export Wizard Limitation for Date Conversion

I'm reading: SQL Server 2008 Import Export Wizard Limitation for Date ConversionTweet this !
Import Export Wizard in SQL Server 2008 has nice improvements over what was available in SQL Server 2005, but still there's more that can and should be done to cater the requirements of the real world.

I feel that while designing of Import Export wizard, equal importance has not been given to each data types. For example, there are properties to adjust the precision and scale when numeric data types are selected. But when datetime data type is selected, there should be option to specify the style to use while making the conversion during the import process. This feature is absent and while making text file imports for example, the dates would be imported and converted to mm/dd/yyyy format i.e. US Standard (provided the installation language is US English). But if the source data contains dates in dd/mm/yyyy format i.e. British Standard, there is no means to specify within the wizard, what format or standard should be used for conversion. This can be a great shortcoming at times.

The workaround can be use another tool, or import the same as Varchar column, and convert is using the Convert system function before using the field. I know that it's not a great solution, but because of the limitation that exists on the date format conversion using this wizard, it becomes a SISO show (Shit In Shit Out). Still it's not that bad and the solution is workable. I hope that Microsoft realizes this shortcoming and adds this feature in a service pack, instead of the next release.

BTW, if anyone is aware of a better solution or would like to correct me on this, I would be glad to learn the same. I might not be completely updated in the usage of SQL Server Tools and it can be my ignorance, but to the best of my knowledge, this shortcoming exists in SQL Server 2008 Import Export Wizard.


Anonymous said...

nice post. thanks.

Aeneas said...

Have the same problem (import dd/mm/yyyy from textfile).
It's been since VB6 I've been messing with regional settings and conversions, you'd wish after all these years MS would know there's more than 1 regional setting in the world and provide some easy ways of converting between them..

Related Posts with Thumbnails