SQL Server Cannot Call Methods on Date

Question

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.

Answer 1

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

Answer 2

It seems to be a real bug as mentioned above by https://stackoverflow.com/users/464923/will, inherited from MS Access old days with all it's inconveniences, You have just to ignore the error message and save your view then run it in a regular SSMS window, no error will be thrown .

Answer 3

I can assume that (ShiftDate AS DATE) should have a datetime format. If you try this:

CAST(CONVERT(DATE, ShiftDate) as Varchar)