I'm in the process of migrating a FoxPro-based system into a SQL Server 2005 database. In the FoxPro system, date fields without values show as ' / / '. When I use the SQL Server Import/Export Wizard, these empty date fields get translated to '12/30/1899 12:00:00 AM' in the resulting SQL Server table.
My question is: what is the easiest way to have SQL Server translate these empty date fields to a NULL rather than the 1899 date? I've had no luck in digging around the Import/Export Wizard, and I don't have enough experience with SSIS packages to know if anything in that avenue would suit my needs.
I'll be importing a number of tables at different times, so the easier and more repeatable the solution, the better. Thanks in advance!
While I appreciate both the suggestions offered, I ended up finding more of a "one and done" solution, rather than running a query on each table after I import them.
I ended up creating an SSIS package and inserting a derived column transformation between the source and destination as needed.
For example, in the Derived Column Transformation Editor, if I had a date column called 'comp_date', and I can say with certainty that any date before 1910 is not valid, my first few columns in the Editor look something like this:
Derived Column Name Derived Column Expression
------------------- -------------- -----------------------------------------
comp_date Replace 'comp_date' DATEPART("year",comp_date) < 1910 ? NULL(DT_DATE):comp_date
This way, I only need to account for the null conversion once when I configure it in the package, then continuously add to the package as more tables get thrown into the mix. This gets me exactly the results I need. Thanks again for the suggestions!