Strange View behaviour

[G_Waddell]

Hi
I have a real conumdrum here - I just can't expalin this behaviour!!

A colleague has designed a view that contains the following : CONVERT (nvarchar(4),YEAR(dbo.SYSFinancialYear.FinancialYearStartDate)) + N'/' + RIGHT ('00' + RTRIM(CONVERT (nvarchar(2), dbo.SYSAccountingPeriod.PeriodNumber)), 2) As YearPeriod

When you are in the design view, of the view and execute you get the value 2011/10 for a particular record which is what we would expect. However if you use the view inside another view or open a query window and run a select statement on the view then for the same record the value changes to 2012/10 If you open the view directly, the value changes back to 2011/10 We've checked and there are no duplicate records or records where the value should be 2012 for this row. The view is returning the value as an nvarchar.

[BitBlt]

I was not able to duplicate your error. More detail on your scenario is necessary. What are the datatypes of the columns? What are the join criteria? Are there selection criteria being used when you execute? What version of SQL Server are you using? How about some sample data?

[adam_k]

This is weird, so I'll give you a weird suggestion. Could it be that there are 2 views with the same name under different owner and the user you are using to login into management studio can't see both views (or you haven't refreshed the list of views, or you are just looking dbo.view_name and the second one is way down to username.view_name).
Why don't you script out the view and drop it. Then try to select from it or use it in a view and let us know what you got.

[G_Waddell]

adam K you were right....