asked on

sql 2008 date field not recognized by sql 2012

I created a view that strips out the date from a datetime field. It shows as a date field in 2008 and functions as a date field when I query it. But when another program on another server tries to access the data (from sql 2012) an error pops up. Note that this other server accessed the data fine until I converted the datetimefield to a date field.

Error message is:
reading the data of the record X failed
Reading the data of the column BookingStartDate failed.
The data type conversion 'dbtype_wVarchar - bddate is not supported

This is the code I used to create the view:
SELECT tblBooking.ID AS BookingId, tblBooking.TimeBookingStart, CAST(TimeBookingStart AS date) AS BookingStartDate
FROM tblBooking

cindyfiller

ASKER

I wanted to add one more piece of information. I went into the sql 2012 database and connected back to the sql 2008 database. I can access other views without an issue, but when I try and access the view with the date field I get a bind error. I've attached it to this comment.
C--Users-csf-Desktop-binding-error.docx

cindyfiller

ASKER

I thought of another thing to add that I tried. I realized that the view I had created accessed data from another view. Accessing the view that included another view worked fine in Sql 2008. As noted above, when I tried to access this view in 2012, I got the binding error. So I removed the view used within this view and I was able to access that test view fine. However, when the vendor tries to access this test view they are still getting the original data conversion error shows on the beginning post. So some progress, but the issue isn't fixed.

Jim Horn

Make sure Mfile_Booking_View is spelled correctly, i.e. no other spaces or underscores.

Make sure it's there, i.e. nobody deleted it, and that it's a table or view and not something else.

Make sure it's the default schema and not something_else.Mfile_Booking_View

Make sure it's in the database where the view is executed, and not another one.

Make sure the user executing the view also has execute privs on Mfile_Booking_View

Try executing the view Mfile_Booking_View, just in case it returns an error because one of the source tables or columns were deleted, or some such thing.

Oh I dunno. Gremlins?

>The data type conversion 'dbtype_wVarchar - bddate is not supported
Smells like a syntax error, such as if somebody changed a column's data type which resulted in the view returning an error. Or maybe bddate is a custom type which was deleted.

cindyfiller

ASKER

I think I know the issue. I can't confirm it until tomorrow when the vendor tries again.

cindyfiller

ASKER

I had fixed the bind error, but our vendor still can't access the file. They continue to get the error I attached above. It doesn't make sense.

You probably realize that I'm the one that was trying to strip out the date from the datetime field that you had replied to yesterday. I did get that code to work fine once I opened a new query window. When I added that code to the view the vendor started having issues accessing the file. They had been successfully accessing it for days until that point. It makes me think there is something in that code that their software doesn't like. I think I need to find a different way to convert the datetime to a date field.

cindyfiller

ASKER

I have 2 updates. First, the vendor found out that they can't use Cast in their module. I'm assuming they also can't access a view that uses that either. I confirmed that the Cast was the issue - I changed the dates to text fields and the issue went away. So I definitely need to do the date field another way.

Jim Horn

> I confirmed that the Cast was the issue
Hmm .. makes no sense

>So I definitely need to do the date field another way.
Fine as long as it's done in the view, and the table's date values stay as dates.

cindyfiller

ASKER

I know!!! Its weird. Any suggestions on the best way to convert it now?

Vitor Montalvão

How the vendor is executing the query?
The user that they're using have all the necessaries permissions?

cindyfiller

ASKER

I don't know a lot because it is a brand new package and right now they are building out some processes for us. I know they have a data connector and it appears to include a query package of sorts. They aren't going to show us this side until the project is in a more complete state. They had checked with support on the package and were they can't use Cast.

Vitor Montalvão

I'm just asking this because they may be using ANSI-SQL instead of T-SQL. You can check this with them?

cindyfiller

ASKER

I'll ask. For now I created another view using the following and am waiting to see if this works. The field actually starts off as a date/time field and they want just the date portion but it has to be a date field.

convert(date,(convert(char(10), TimeBookingEnd, 101))) as BookingEndDate,

Vitor Montalvão

convert is a T-SQL function. On ANSI SQL you'll only have cast function.

cindyfiller

ASKER

Thanks for the clarification. Since the cast function doesn't work, hopefully the convert will! I'm hoping they can test it this morning and I'll let you know.

cindyfiller

ASKER

The above convert's didn't work either. You can kind of see the way they are selecting the data with their tool along with the error. I've verified that the fields are listed as DATE fields, but their package is always trying to convert them.

Had to copy the print screen to a word doc and attach it as a word doc
C--Users-csf-Desktop-error-message.docx

Vitor Montalvão

You'll need to sort out with them to find out what component they are using. Without that information it will be hard for us to help you.

cindyfiller

ASKER

It doing further testing, it works fine if the field is a date/time field, but not if it is a date field. I've stripped out the time and left it a date/time field and will leave it at that.

Thanks for your help.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

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

Start Free Trial