CREATE VIEW [dbo].[DateRangeExpanded] WITH SCHEMABINDINGResults in
AS
select n.Number, n.Date
from dbo.Numbers n
where n.Date >= CONVERT(DATETIME,'2007-12-01') and
n.Date <= datediff(day, 0, DATEADD(MONTH, 6, GETDATE()))
Error TSD03127: Cannot schema bind view '[dbo].[DateRangeExpanded]'WFT? SQL Server is quite happy with the above, it's just GDR getting it's knickers in a twist yet again: DateTime is a type not a column. I have found no way of making it happy with this one[1], other than taking the schemabinding off, and I am pretty pissed about limiting my database design just to accommodate GDR's inadequacies.
because name 'DATETIME' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself.
If this project wasn't finishing up in a few weeks, GDR would be in the bin by now.
[1] CAST vs CONVERT doesn't make any difference, for one thing.
why do you convert/cast your datetime string, SQLServer is happy to do that for you. If n.Date is a Datetime column so to speak.
ReplyDeleteProbably spending too much time doing Oracle.
ReplyDeleteAnd doesn't it sometimes (when you directly compare to a date literal) convert the date column to a string for the comparison (not the other way round), which defeats the use of indexing on the date column?