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
LVL 44
zephyr_hex (Megan)DeveloperAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Anthony PerkinsConnect With a Mentor 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
0
 
pkumarraCommented:
Can you give details of how you are importing the data?
0
 
David ToddConnect With a Mentor Senior DBACommented:
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
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
David ToddSenior DBACommented:
Hi AC,

OK.

Cheers
  David
0
 
LowfatspreadConnect With a Mentor 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.
0
 
zephyr_hex (Megan)DeveloperAuthor 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

0
 
zephyr_hex (Megan)DeveloperAuthor 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.
0
All Courses

From novice to tech pro — start learning today.