SQL server 2012 error converting date from string when selecting date with like

Question

In my table, I have a datetime NULL field called logDate. The format stored: 2014-03-28 12:24:00.000

I have a form and the log date is one of the fields for searching logs. The user will enter the date like 2014-03-28

So in my SELECT procedure I need to use a LIKE:

@logDate datetime =NULL
.
.
       SELECT .. FROM mytable WHERE
     (@logDate IS NULL OR CONVERT(VARCHAR, @logDate, 102) LIKE '%'+logDate+'%')

I execute the procedure:

EXEC dbo.SELECT_mytable @logDate= '2014-03-28'

But I get the following error:

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

What am I doing wrong?

Why aren't you using BETWEEN with datetime values instead, or just a conversion to `date`? Avoid string conversions where possible... — Jon Skeet, Apr 01 '14 at 08:36
Is not event he string conversion, is the predicate [SARGability](http://en.wikipedia.org/wiki/Sargable). By using LIKE on date any hope of using a index range scan is thrown out, the query is guaranteed to to an complete end-to-end scan. — Remus Rusanu, Apr 01 '14 at 09:06
DATETIME and DATE data types are not stored in any format. The format you see is a product of various things such as the client tool, the language etc. — Nick.Mc, Apr 11 '14 at 00:52

connectedsoftware · Accepted Answer · 2014-04-01T09:01:10.727

You also need to convert the logdate column to a varchar, I think you have your LIKE the wrong way around as you are trying to find the user entered date within the date column, so try:

SELECT .. FROM mytable WHERE
    (@logDate IS NULL
     OR '%'+CONVERT(VARCHAR, @logDate, 102)+'%' LIKE CONVERT(VARCHAR, logDate, 102))

As others have indicated (and I should have pointed out) you shouldn't be converting Dates to Strings in-order to search date columns, much better to keep everything in a DateTime format for performance.

This will work, provided that you change your stored procedure to expect the @logDate parameter as a DateTime:

SELECT .. FROM mytable WHERE
    (@logDate IS NULL
     OR logDate = @logDate)

I get the impression that you went down the string comparison route because you wanted to ignore the time element and just search on date, if that is the case you can strip the time from both elements and just match on date by doing this:

IF @logDate IS NOT NULL
 BEGIN
   // Remove any time element
   SET @logDate = DATEADD(dd,0, DATEDIFF(dd,0,@logDate))
 END

SELECT .. FROM mytable WHERE
    (@logDate IS NULL
     OR DATEADD(dd,0, DATEDIFF(dd,0,logDate)) = @logDate)

You should compare dates NOT strings. – Kaf Apr 01 '14 at 08:46 — Kaf, Apr 01 '14 at 08:46

SQL server 2012 error converting date from string when selecting date with like

1 Answers1