Error converting data type DBTYPE_DBTIMESTAMP to datetime

Question

Error converting data type DBTYPE_DBTIMESTAMP to datetime

cdp-1134584

Old Hand

Points: 302
More actions
July 27, 2009 at 6:33 pm

#133510

Help pls..
I am trying to import data from dbase(legacy app) to sql server. In legacy app the date field is a free text field without any validation. So there are lot of typos in data entry like user entered 0207 instead of 2007 for year. dbase accepted this date. But when trying to import the data to sql server I am getting above error. Tried cast/convert/isdate in my select statement like below and none of them worked.
select * FROM OPENROWSET(
'MSDASQL',
'Driver={Microsoft dBase Driver (*.dbf)};DBQ=C:\Mytest\Ham 2.0\',
'Select convert(varchar(10),dateapplph,101) from customer')
I know I can update dbase to fix out of range dates but what I am trying to here is developing an application for support team so that they can import data of each customer.
I appreciate your help in this regard.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

john.arnott SSChampion Points: 11882 More actions · Answer 1

If the legacy app data doesn't conform to the expectations of the CAST or CONVERT for a datetime, then those functions cannot work. SQL has to be able to interpret the value as a date and time in the range of January 1, 1753 (the year after the adjustment to the Gregorian calendar) to December 31, 9999, and must be in one of the formats it knows how to parse out. For instance, you can take a properly formed date that's within the supported range as your input to a CONVERT to a datetime field, but if the input doesn't meet the criteria, you'll get an error. This works:declare @mydate datetime

,@mydate2 datetime

,@date001 char(20)

,@date002 char(20)

set @date001= '2007/01/01'

set @date002= 'Apr 1, 2007'

set @mydate = convert(datetime,@date001)

set @mydate2= convert(datetime,@date002)

select @mydate, @mydate2

But this gives a couple of errors:declare @mydate datetime

,@mydate2 datetime

,@date001 char(20)

,@date002 char(20)

set @date001= '1007/01/01'

set @date002= 'Aprl 1, 2007'

set @mydate = convert(datetime,@date001)

set @mydate2= convert(datetime,@date002)

select @mydate, @mydate2

So, if you want to put these data in "datetime" columns in SQL Server, they'll have to be edited/fixed first. I'm not sure that you want to try to automate that step in SQL. If you want to import the unfiltered data from the legacy app warts and all, with no editing, then the target column should probably be a varchar.

cdp-1134584 Old Hand Points: 302 More actions · Answer 2

John,

Thanks for reply. But as I said there are hundreds of customer databases and correcting each datetime column of each table is out of scope. If there is anyway of selecting the data from this table using OpenQuery (like select * from table1 which has dates in out of range) I should be fine.

From what I understand looks like sql server is treating this column as datetime. Is there anyway sql server can treat this column as varchar...

john.arnott SSChampion Points: 11882 More actions · Answer 3

If the source date information is in a "free text field", I would think that would mean it's simply a string. Yet, you're getting some error about converting DBTYPE_DBTIMESTAMP. Is that because you're directing the data to a table with a datetime column?

Do you have the definition of the source? What's the full statement that fails?

What's the full error message you get?

prasadn-672538 Grasshopper Points: 17 More actions · Answer 4

John,

I have not yet gone upto insert. Just selecting itself is a problem. Here is the statement that I am executing

select * FROM OPENROWSET(

'MSDASQL',

'Driver={Microsoft dBase Driver (*.dbf)};DBQ=C:\Mytest\Ham 2.0\',

'Select dateapplph, * from customer')

and the error message I am getting is

Error converting data type DBTYPE_DBTIMESTAMP to datetime and this error is because there is a date "03/01/0207". As long as my select works I can Manage my insert.

Thanks a lot for your time..

john.arnott SSChampion Points: 11882 More actions · Answer 5

I'm not familiar with dBase, but it looks as though it must allow the bad data in a field/column that it has identified as a date (else, where would MS SQL get the notion that "dateapplph" is a date?). I'll ask around, but beyond thinking that using SSIS with a data conversion task may work, I don't think I can directly help with this one.

David Burrows SSC Guru Points: 65144 More actions · Answer 6

I had a similar problem with a third party database and driver. The error is as you stated are the invalid dates (as far as SQL Server is concerned) and SQL Server is treating the column as DATETIME because that is what it found the column to be.

I got round it by using YEAR,MONTH and DAY in the query in OPENQUERY to get the parts of the date and than converted it using CASE or concatenating to char. Note that YEAR etc are done by the source engine as SQL Server will never be able to process the dates.

Do not know if dBase has YEAR,MONTH DAY functions been a long while since I played with dBase.

Far away is close at hand in the images of elsewhere.
Anon.

Jack Corbett SSC Guru Points: 184394 More actions · Answer 7

I'm pretty sure dbase has a CAST function so you could CAST you date columns to string.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question