SQL Server存储过程——将数据类型varchar转换为datetime的错误

I have this stored procedure:

我有这个存储过程:

ALTER PROCEDURE [dbo].[PostScheduledTasks] 
    @actualStart datetime = NULL, 
    @actualFinish datetime = NULL,
    @actualEndDate datetime = NULL,
    @UserDate1 datetime = NULL,
    @IsCompleted bit = 0,
    @PercentComplete float = 0.0,
    @UStmp varchar(10) = NULL,
    @SchedukeTaskID int = 0,
    @SortOrder int = 0
AS
BEGIN
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    UPDATE ScheduleTasks  
    SET ActualStart = @actualStart,
        ActualFinish = @actualFinish,
        ActualEndDate = @actualEndDate,
        UserDate1 = @UserDate1,
        IsCompleted = @IsCompleted,
        PercentComplete = @percentComplete,
        UStmp = @UStmp
    WHERE 
        ScheduleTaskID = @SchedukeTaskID
        AND SortOrder = @SortOrder
END

and when I try to execute it:

当我尝试执行它时

DECLARE @return_value int

EXEC    @return_value = [dbo].[PostScheduledTasks]
        @actualStart = N'08-03-2016 8:00:00 AM',
        @actualFinish = N'08-03-2016 4:00:00 PM',
        @actualEndDate = N'08-03-2016 4:00:00 PM',
        @UserDate1 = N'27-01-2016 8:00:00 AM',
        @IsCompleted = 1,
        @PercentComplete = 1,
        @UStmp = N'jsuske',
        @SchedukeTaskID = 597295,
        @SortOrder = 19

SELECT  'Return Value' = @return_value
GO

I get this error:

我得到这个错误:

Error converting data type varchar to datetime.

将数据类型varchar转换为datetime的错误。

1 个解决方案

#1

The safest way is to provide dates as ISO 8601 which is culture independent:

最安全的方法是提供与文化无关的ISO 8601的日期:

yyyy-mm-ddThh:mm:ss[.mmm]

yyyy-mm-ddThh:mm:ss(.mmm)

The advantage in using the ISO 8601 format is that it is an international standard. Also, datetime values that are specified by using this format are unambiguous. Also, this format is not affected by the SET DATEFORMAT or SET LANGUAGE settings.

使用ISO 8601格式的优点是它是一个国际标准。此外，使用这种格式指定的datetime值是明确的。此外，这种格式不受SET DATEFORMAT或SET语言设置的影响。

DECLARE @return_value int

EXEC    @return_value = [dbo].[PostScheduledTasks]
        @actualStart     = N'2016-03-08T08:00:00',
        @actualFinish    = N'2016-03-08T16:00:00',
        @actualEndDate   = N'2016-03-08T16:00:00',
        @UserDate1       = N'2016-01-27T08:00:00',
        @IsCompleted     = 1,
        @PercentComplete = 1,
        @UStmp           = N'jsuske',
        @SchedukeTaskID  = 597295,
        @SortOrder       = 19

SELECT  'Return Value' = @return_value

Or you have to check SET LANGUAGE/DATEFORMAT and provide date in correct format.

或者您必须检查设置语言/日期格式并以正确的格式提供日期。

#1