Andrew McMenemy Andrew McMenemy -4 years ago 112
SQL Question

SQL DateTime Conversion Error on Update Command

trying to update existing entries in my SQL database from Visual Studio using Stored Procedures. The error I get when debugging the actual SQL statement is:

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


Even when I stub in both NULL or proper time formats.
Here's the SQL Code

ALTER PROCEDURE [dbo].[SP_UpdateQuery] (
@NewName NVARCHAR(25) = NULL,
@Name NVARCHAR(25) = NULL,
@Surname NVARCHAR(25) = NULL,
@NewSurname NVARCHAR(25) = NULL,
@CompanyName NVARCHAR(25) = NULL,
@NewCompanyName NVARCHAR(25) = NULL,
@Address NVARCHAR(35) = NULL,
@NewAddress NVARCHAR(35) = NULL,
@MobileNo NVARCHAR(12) = NULL,
@NewMobileNo NVARCHAR(12) = NULL,
@PhoneNo NVARCHAR(12) = NULL,
@NewPhoneNo NVARCHAR(12) = NULL,
@Email NVARCHAR(40) = NULL,
@NewEmail NVARCHAR(40) = NULL,
@AreaCode NVARCHAR(10) = NULL,
@NewAreaCode NVARCHAR(10) = NULL,
@DateTime DateTime = NULL,
@NewDateTime DateTime = NULL
)
AS
UPDATE [dbo].[CUSTOMERS]
SET Name = @NewName, Surname = @NewSurname, Email = @NewEmail, MobileNo = @NewMobileNo, PhoneNo = @NewPhoneNo,
CompanyName = @NewCompanyName, Address = @NewAddress, AreaCode = @NewAreaCode, DateTime = @NewDateTime
WHERE (@Name IS NULL OR Name LIKE '%' + @Name + '%')
AND (@Surname IS NULL OR Surname LIKE '%' + @Surname + '%')
AND (@Email IS NULL OR Email LIKE '%' + @Email + '%')
AND (@MobileNo IS NULL OR MobileNo LIKE '%' + @MobileNo + '%')
AND (@PhoneNo IS NULL OR PhoneNo LIKE '%' + @PhoneNo + '%')
AND (@Address IS NULL OR Address LIKE '%' + @Address + '%')
AND (@CompanyName IS NULL OR CompanyName LIKE '%' + @CompanyName + '%')
AND (@Email IS NULL OR Email LIKE '%' + @Email + '%')
AND (@AreaCode IS NULL OR AreaCode LIKE '%' + @AreaCode + '%')
AND (@DateTime IS NULL OR DateTime LIKE '%' + @DateTime + '%')


Existing time entries in the table are as such

2016-11-10 00:00:00.000


Again, the error occurs when actually debugging the SQL, so nothing to fix in Visual Studio just yet.

My test cases have been allowing the Time variables to default to NULL and also inputting these values

@DateTime = N'2016-11-10T00:00:00',
@NewDateTime = N'1900-01-01T01:01:01'
//New Test Case
@DateTime = N'1900-01-01 00:00:00',
@NewDateTime = N'1900-01-01 01:01:01'


The most similar questions I could find ended up being errors in C# Code, but since the SQL is actually bugging out here, could use some help. Thanks for you time!

Answer Source

As mentioned in comment, Replace

AND (@DateTime IS NULL OR DateTime LIKE '%' + @DateTime + '%')

with

AND @DateTime IS NULL
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download