asked on

Problem with datetime data type when importing table to SQL Server 2005

i'm importing data from a non-sql database to sql server 2005

with one table, it is giving an error on import concerning conversion from DBTYPE_DBTimestamp to datetime

i have the data type set to datetime
upon reviewing the data i'm trying to import, i can see that some records are NULL in the date column. would this cause the conversion error, and if so, how do i handle it?

Thanks

pkumarra

Can you give details of how you are importing the data?

SOLUTION

David Todd

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Todd

Hi AC,

OK.

Cheers
David

SOLUTION

Lowfatspread

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

zephyr_hex (Megan)

ASKER

i dont think it's precision. i have 2 databases that i'm importing from... both are identical structure, with differing values in the tables. these databases are the backend for our ERP (Sage MAS 90/200)

i am only having this issue in one of the two databases... if it were precision, i would expect the problem in both (as the date values are created by the ERP application)

i have not restricted nulls on any columns.
i was thinking about adding a statement to exclude the rows with null values for the datetime from the import...

delete from SO_SalesOrderHeaderQM9
insert SO_SalesOrderHeaderQM9(SalesOrderNo,OrderDate,OrderType,OrderStatus,ShipExpireDate,ARDivisionNo,CustomerNo,ShipToCode,ShipToName,ShipToAddress1,ShipToCity,ShipToState,ShipVia,CustomerPONo,FOB,WarehouseCode,TermsCode,SalespersonDivisionNo,SalespersonNo,CancelReasonCode,FreightAmt,DateCreated,TimeCreated,UserCreatedKey,DateUpdated,TimeUpdated,UserUpdatedKey,SOMC_234_EntryCurrency,SOMC_234_EntryRate,UDF_INTERNAL_NOTE_HDR,UDF_PPL_Code)
select SalesOrderNo,OrderDate,OrderType,OrderStatus,ShipExpireDate,ARDivisionNo,CustomerNo,ShipToCode,ShipToName,ShipToAddress1,ShipToCity,ShipToState,ShipVia,CustomerPONo,FOB,WarehouseCode,TermsCode,SalespersonDivisionNo,SalespersonNo,CancelReasonCode,FreightAmt,DateCreated,TimeCreated,UserCreatedKey,DateUpdated,TimeUpdated,UserUpdatedKey,SOMC_234_EntryCurrency,SOMC_234_EntryRate,UDF_INTERNAL_NOTE_HDR,UDF_PPL_Code
from OPENQUERY(QM9LIVE,'SELECT SalesOrderNo,OrderDate,OrderType,OrderStatus,ShipExpireDate,ARDivisionNo,CustomerNo,ShipToCode,ShipToName,ShipToAddress1,ShipToCity,ShipToState,ShipVia,CustomerPONo,FOB,WarehouseCode,TermsCode,SalespersonDivisionNo,SalespersonNo,CancelReasonCode,FreightAmt,DateCreated,TimeCreated,UserCreatedKey,DateUpdated,TimeUpdated,UserUpdatedKey,SOMC_234_EntryCurrency,SOMC_234_EntryRate,UDF_INTERNAL_NOTE_HDR,UDF_PPL_Code FROM SO_SalesOrderHeader')

Open in new window

zephyr_hex (Megan)

ASKER

well, i found the issue..
it wasn't the NULL records...
there was an instance of an actual bad date in one row.. something like 1/1/507

awarding points because the discussion was informative.