We help IT Professionals succeed at work.

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
Comment
Watch Question

Commented:
Can you give details of how you are importing the data?
David ToddSenior Database Administrator
CERTIFIED EXPERT
Commented:
Hi,

A SQL timestamp is equivalent to a SQL binary( 8 ) value. It isn't a time or date at all.

Look up timestamp in BOL
http://msdn2.microsoft.com/en-us/library/ms182776.aspx

Cheers
  David
CERTIFIED EXPERT
Top Expert 2012
Commented:
David,
I am going to differ with you on that one.  DBTYPE_DBTimestamp is the ADO data type for SQL Server datetime.

See here:
http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx
David ToddSenior Database Administrator
CERTIFIED EXPERT

Commented:
Hi AC,

OK.

Cheers
  David
CERTIFIED EXPERT
Top Expert 2011
Commented:
the null could be the problem ... have you allowed your table to accept nulls for the column

another problem could be the precision to which the other systems timestamp is set
e.g. db2/udb goes to millionths of seconds rather than sql servers just thousandths of seconds


ps .. i agree with anthony on the dbtype issue.
CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
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

CERTIFIED EXPERT
Top Expert 2010

Author

Commented:
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.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.