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?
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.

pkumarraCommented:
Can you give details of how you are importing the data?
0
David ToddSenior 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
Anthony PerkinsCommented:
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

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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

David ToddSenior DBACommented:
Hi AC,

OK.

Cheers
  David
0
LowfatspreadCommented:
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
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
Microsoft SQL Server 2005

From novice to tech pro — start learning today.