CODEWITHSUNDEEP
sqlsql-servert-sqldatetimestored-procedures
user979331
user979331

Reputation: 11951

SQL Server stored procedure - error converting data type varchar to 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.

Upvotes: 2

Views: 8596

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

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

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.

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.

Upvotes: 5

Related Questions