I've got a DATETIME column in a SQL Server 2008 table called ShiftDate. I want to convert this to a DATE column in a query:

SELECT     ID, ScheduleID, ShiftDate, CONVERT(DATE, ShiftDate) AS ProductionDate
FROM       dbo.ScheduleResults

I am editing this query in SSMS. If I run the query in a standard query window, I don't get any errors. If I run this in the View editor window, I get the error "Cannot Call Methods on Date".

I've tried the CAST method but it gets the same error.

SELECT     ID, ScheduleID, ShiftDate, CAST(ShiftDate AS DATE) AS ProductionDate
FROM       dbo.ScheduleResults

The full error message is:

Executed SQL statement: SELECT ID, ScheduleID, ShiftDate, CAST(ShiftDate as DATE).ToString() AS ProductionDate FROM dbo.ScheduleResults
Error Source: .Net SqlClient Data Provider
Error Message: Cannot call methods on date.

I am inclined to think this is a bug in SSMS, but I would like to get some feedback from StackOverflow folks as to how to convert a datetime column to a date. I can easily convert it to a string column, but it is not ideal as Excel will not see it as a date.

You are correct, it is a bug in SSMS. I'm using SQL Server Management Studio 2008 R2 and when I try to create a View using the built-in designer, I get the same error message as you:

SQL Execution Error
Error Source: .Net SqlClient Data Provider
Error Message: Cannot call methods on date.

As @Aaron Bertrand mentioned, to solve the issue, select 'New Query' and create the View in the Query window. For your code, it would be:

CREATE VIEW myView AS
SELECT     ID, ScheduleID, ShiftDate, CAST(ShiftDate AS DATE) AS ProductionDate
FROM       dbo.ScheduleResults

more