I used a BCP to download data from server1, table1 into a txt file. Now I am trying to BULK INSERT from the txt file into server2, table1. There is a datetime1 column in table1 that allows NULLs. The NULL are a hex 00 in the txt file. Trying to BULK INSERT the NULL into table1 is giving me problems. And Yes, Table1, datetime1 allows NULL values.

Things that i have tried
1) BULK INSERT directly from the txt files gives the following. Row 3 has the ~ ~ value for rundate_end. Tilda is the delimiter and the value is a hex 00.

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 3, column 5 (RunDate_End).

2) Add KEEPNULLS to the BULK INSERT gives the exact same error as #1

3) I gave RunDate_End column a default value of 01/01/1900. This gave the same error as #1

4) In notepad edited the file to change all ~ ~ to ~ ~. Which changed the hex 00 to a hex 20. This worked. But first, I do not want 01/01/1900 in the RUnDate_End and second, I don't want to have to edit the file.

How can I BULK INsert NULLS (hex 00) into a DateTime field?

-- export ExportTable to txt file

DECLARE @bcp varchar(max) = 'bcp "SELECT * from ExportTable" queryout "c:\ExportOut.txt" -e "c:\ExportOutError.txt" -d StoresDB1 -c -T -t~';
EXEC xp_cmdshell @bcp;

-- Move txt file to another remote server, import txt file into ExportTable

BULK INSERT StoresDB2.dbo.ExportTable
FROM 'd:\Export\ExportOut.txt'
WITH (
     BATCHSIZE      = 50000
    ,CODEPAGE       = 'RAW'
    ,DATAFILETYPE   = 'char'
    ,FIRSTROW       = 2
    ,FORMATFILE     = 'd:\Export\ExportOut.fmt'
    ,MAXERRORS      = 2000000000
    ,ERRORFILE      = 'd:\Export\Error\ExportOut.err'  
    ,TABLOCK
    );

You may have to preprocess the file before using it as input with BULK INSERT:

http://stackoverflow.com/questions/24942538/sql-wont-insert-null-values-with-bulk-insert

Alternate, use SSIS Import/Export Wizard:

How to use the SSIS Import/Export Wizard?