CODEWITHSUNDEEP
sqlsql-serverstringdatetimestored-procedures
CiucaS
CiucaS

Reputation: 2128

Conversion failed when converting date and/or time from character string in a stored procedure

So I try to run this query

ALTER PROCEDURE [dbo].[sp_ImportMonetareWinmentor]
    @dataStart datetime ,
    @dataFinal datetime
AS

    set language english
        declare @erroare int

        if(@dataStart > @dataFinal) set @erroare = -1;  set language english
        declare
                  @dataStart datetime = '2011-05-15T00:47:00' ,
                  @dataFinal datetime = '2011-05-17T15:43:00'
                  print @dataStart 
                  print @dataFinal
                  EXEC [sp_ImportMonetareWinmentor] @dataStart,@dataFinal

My stored procedure has two datetime parameters:

ALTER PROCEDURE [dbo].[sp_ImportMonetareWinmentor]
    @dataStart datetime ,
    @dataFinal datetime
AS
set language english
    declare @erroare int

    if(@dataStart > @dataFinal) set @erroare = -1;
            else begin  
                print('Procedure')
        return @erroare
                end
        -- Insert statements for procedure here
    END

Any idea why I get the error

Conversion failed when converting date and/or time from character string.

I know it has something to do with the datetime, but I can't see what I'm doing wrong, or why does SQL want to convert my input param to date from string as they are already string.

Upvotes: 0

Views: 1361

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

YYYY-MM-DD hh:mm isn't unambiguous, so far as SQL Server is concerned - it will sometimes try to interpret it as YYYY-DD-MM hh:mm and so trips over when it doesn't know which the 17th month is. Try:

declare
      @dataStart datetime = '2011-05-15T00:47:00',
      @dataFinal datetime = '2011-05-17T15:43:00'

Demonstration of the problem. This:

set language british
go
declare
          @dataStart datetime = '2011-05-15 00:47',
          @dataFinal datetime = '2011-05-17 15:43'

Generates the error. This:

set language english
go
declare
          @dataStart datetime = '2011-05-15 00:47',
          @dataFinal datetime = '2011-05-17 15:43'

doesn't.

Upvotes: 2

Related Questions