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
cindyfillerDirector of ITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

cindyfillerDirector of ITAuthor Commented:
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.
cindyfillerDirector of ITAuthor Commented:
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 HornMicrosoft SQL Server Data DudeCommented:
<Potentially stupid answers>
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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

cindyfillerDirector of ITAuthor Commented:
I think I know the issue.  I can't confirm it until tomorrow when the vendor tries again.
cindyfillerDirector of ITAuthor Commented:
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.
cindyfillerDirector of ITAuthor Commented:
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 HornMicrosoft SQL Server Data DudeCommented:
> 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.
cindyfillerDirector of ITAuthor Commented:
I know!!!  Its weird.  Any suggestions on the best way to convert it now?
Vitor MontalvãoMSSQL Senior EngineerCommented:
How the vendor is executing the query?
The user that they're using have all the necessaries permissions?
cindyfillerDirector of ITAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
I'm just asking this because they may be using ANSI-SQL instead of T-SQL. You can check this with them?
cindyfillerDirector of ITAuthor Commented:
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ãoMSSQL Senior EngineerCommented:
convert is a T-SQL function. On ANSI SQL you'll only have cast function.
cindyfillerDirector of ITAuthor Commented:
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.
cindyfillerDirector of ITAuthor Commented:
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
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
cindyfillerDirector of ITAuthor Commented:
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.
Vitor MontalvãoMSSQL Senior EngineerCommented:
That can means that their application don't recognizes Date field (only date without time). They're using another layer between their application and the database and that's why it's hard to analyze it but you're doing fine with all those tests.

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
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
Query Syntax

From novice to tech pro — start learning today.