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.