SQL Server 无法在日期上调用方法 [英] SQL Server Cannot Call Methods on Date

问题描述

我在名为 ShiftDate 的 SQL Server 2008 表中有一个 DATETIME 列.我想在查询中将其转换为 DATE 列:

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

我正在 SSMS 中编辑此查询.如果我在标准查询窗口中运行查询，则不会出现任何错误.如果我在视图编辑器窗口中运行它，我会收到错误无法在日期调用方法".

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".

我已经尝试了 CAST 方法，但它得到了同样的错误.

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

完整的错误信息是:

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.

我倾向于认为这是 SSMS 中的一个错误，但我想从 StackOverflow 人员那里获得一些关于如何将日期时间列转换为日期的反馈.我可以轻松地将其转换为字符串列，但它并不理想，因为 Excel 不会将其视为日期.

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.

推荐答案

你说得对，这是 SSMS 中的一个错误.我使用的是 SQL Server Management Studio 2008 R2，当我尝试使用内置设计器创建视图时，我收到与您相同的错误消息:

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.

正如@Aaron Bertrand 提到的，要解决该问题，请选择新查询"并在查询"窗口中创建视图.对于您的代码，它将是:

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

这篇关于SQL Server 无法在日期上调用方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！