CODEWITHSUNDEEP
sql-servert-sqldatetimetype-conversionvarchar
Kim Crosser
Kim Crosser

Reputation: 433

Conversion failed when converting varchar to datetime

This appears to be a bug in SQL Server itself. I have a VERY simple function, whose job is to basically emulate try_convert(datetime, string arg), since my customer won't allow setting the database to SQL Server 2012 compatibility.

I extracted out the start of the function and the test query, as follows:

create function spTryConvertDateTime (@pString varchar(255))
returns datetime
as
begin
   declare @iresult datetime = null;
   declare @string varchar(255) = ltrim(rtrim(isnull(@pString, '')));
   declare @datestring varchar(255);
   declare @timestring varchar(255);

   if len(@string) < 10
      return @iresult;

   set @datestring = left(@string, 10);

   if len(@string) >= 19
      set @timestring = substring(@string, 11, len(@string-10));

   return null;
end;
go

declare @v varchar(32) = '2017-10-31 00:00:00';
select dbo.spTryConvertDateTime(@v);

When I try to run this, I get the following error:

Msg 245, Level 16, State 1, Line 3
Conversion failed when converting the varchar value '2017-10-31 00:00:00' to data type int.

??? Where am I trying to convert ANYTHING to data type int?

What is going on? What am I missing?

Upvotes: 1

Views: 208

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You have this code deep in the function:

len(@string-10)

(This is the end of the last line before the return NULL.)

You cannot subtract 10 from a string. Hence the attempt to convert it to an integer.

Upvotes: 1

Related Questions