How to check within a format file whether a date field has the value "00000000" and if so, replace with a null?

I have to convert several Oracle's CTL script files to a SQL Server 2014 format files.  I have 90% of the conversion complete, with the exception of the date fields.  Within the DAT file that we receive from our client, if a date value is not given, the value of "00000000" is specified (as oppose to leaving the field null).  

When I try to do a BULK INSERT, I get an error message indicating "Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row #, column 11 (ADMSN_DT)".  The cause of the error is the string value "00000000".

Is there a way of specifying the following Oracle statement in T-SQL of a format file?

ADMSN_DT                   DATE 	'YYYYMMDD' NULLIF ADMSN_DT="00000000"

Open in new window

My format file has the field defined as data type "DATE".  Is there a way of imbedding logic into the format file to null out the field if it reads the value "00000000" for a give date field (as done in Oracle)?  

thank  you.
Maria TorresData AnalystAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
bring it in as a varchar. then check for ISDATE() and NULLIF(yuorcolumn,'000000')

Depending on what version of SQL Server - you can also do a TRY_CONVERT()  instead of isdate()

And you can embed them   try_convert(datatype,(nullif(yourcolumn,'000000')), stylecode)

where style code tells convert what format your string is :

nullif (2008):
isdate (2008):
try_convert (2012):

declare @datetime2 datetime2
declare @datetime datetime
declare @date date

set @date = try_convert(date,'00000000',100)
set @datetime = try_convert(datetime,'00000000',100)
set @datetime2 = try_convert(datetime2,'00000000',100)

select @date,@datetime,@datetime2 

Open in new window

You cannot tell the FORMAT FILE how to decipher the string. You will have to do it after the data is loaded.

So, import into a "staging" file first, then put away into the appropriate tables.
Maria TorresData AnalystAuthor Commented:
Hi Mark,

I'm not a SQL Developer, and I do not know what you mean by "import into a staging file".  Can you please explain or point me to a site where I can get further information on this?  Thank you.
Scott PletcherSenior DBACommented:
Easiest way is probably to use SSIS rather than BULK INSERT statement.

SSIS allows data conversion/transform step(s) so you could change the data prior to load.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Mark WillsTopic AdvisorCommented:
A staging table is a real table with a unique name so it doesnt become confused with the live / production data.

Even something as simple as the prefix TMP_STAGING_.... with the correct column names, just make any contentious datatypes VARCHAR or NVARCHAR so you can inspect and fix.

The other strategy is to make all columns the correct data type and address each error as you encounter them and try again. You can keep dropping / creating the table as often as needed, but that can get very frustrating.

The idea behind a staging table is you can import into that table, do any manipulations / transformations within that, and when happy with the data, update / insert into the live table

Does that make sense ?
Maria TorresData AnalystAuthor Commented:
Mark, thanks for clarifying what a staging table is.  I'm going to try it right after I try one more thing with the format file.  I just read a document on how to use a format file to skip a data field.  I'm going to try to see if I can skip the four date fields that have the value as "00000000".  If the format file does not work, then I will try the staging table.

I'll let you know the outcome.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark WillsTopic AdvisorCommented:
You can skip fields in a format file (use 0), but it will skip every occurrence - even the 'good' dates unfortunately.

How are you importing ?

If using OPENROWSET, you have the chance to intercept as part of the select and can use either try_convert() or NULLIF()

If you would like to share some sample data and your format file, I could set up a sample for you.
Maria TorresData AnalystAuthor Commented:
From my understanding the four date fields that are giving me trouble will always have the value "00000000", because they are considered to be confidential fields.  So, I can skip them without any concern.

However, I do have a question.  I ran the BULK INSERT.. format file and was able to load 500K records into the table.  Only three records failed to load.  I did get an error report which states the lines that failed and error code "HRESULT 0x80004005".  I looked through the web but all I found is that I should make sure that each line ends with carriage return code.  (I looked through my file with Notepad++ and all lines are ending correctly.)  Is there a more meaningful way of getting the name of the field and a description of why it failed (e.g., field size too large)?  In Oracle, we are able to populate an error report that is informative (i.e., line, field name, and error description).  

Any input is greatly appreciated.  Thank you.
Maria TorresData AnalystAuthor Commented:
The Microsoft document that I read provided me with the solution to my problem.  By specifying the value of zero to the format's columns "Host File Data Length" and "Server Column Name", I can bypass the specific date fields that have a string value "00000000" and proceed with the loading of the data.
Mark WillsTopic AdvisorCommented:
Yep, think I said you can skip fields...

Little bit disappointed that my contributions didnt assist resolving your problem. Ah well :)
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.