I see you didn't read the thread really. Try an exact match without a string conversion and we would talk on it after that.


>I just don't understand why you would ever use LIKE in a date query. Can you enlighten me on a use case that isn't easier solved another way?
>
>IOW:
>
> select * from wws_invoice where invDate like 'Jun%'
> select * from wws_invoice where DatePart(mm, invdate) = 8
>
>Anything more complicated on that string format will be difficult to make into anything useful especially if you are dynamically building a query.
>
>And for any typical date queries you're going to use > and < comparisons or ranges anyway.
>
>select * from wws_invoice where invdate > '2017-08-01'
>
>> >"The accepted way" - who accepted that way which kills index usage completely!!!
>
>Both have the same execution plan requiring a full index scan.
>
>+++ Rick ---
>
>
>So what exactly do you plan to make dynamic with a query like this? How do you write a query with that syntax to say greater than 07/2015?
>
>Now you can effectively do stuff like this:
>
>select * from wws_invoice where invdate > '2017-08-01' and invdate < 2017-08-02
>
>and that's accepted date string syntax, but where does LIKE fit in there? I just would like to hear a use case where that could possibly make sense - especially given the long form date syntax in your first example.
>
>
>So you think doing a string conversion using CAST will not do a full table scan?
>
>+++ Rick ---
>
>
>>I have already explained in my first reply to Dragan, here it is again.
>>
>>"Using like automatically will treat the date like a string?"
>>
>>Yes, exactly. As documented in LIKE help if the datatype is not a string it is attempted to convert it to a string. And as documented in cast\convert documentation, default datetime conversion to string is type 0 (,100) which is mon dd yyyy hh:miAM (or PM).
>>
>>If you have a date like this in the DB:
>>

>>2015-06-01 11:52:59.057
>>

>>
>>and you do queries like this:
>>
>>

>>select * from wws_invoice where invdate like 'Jun%'
>>select * from wws_invoice where invdate like 'Jun 1%'
>>select * from wws_invoice where invdate like 'Jun 1 %'
>>select * from wws_invoice where invdate like 'Jun 1 2015:%'
>>select * from wws_invoice where invdate like 'Jun ? 2015%'
>>...
>>select * from wws_invoice where invdate like 'Jun 1 2015 11:52AM '
>>
>>

>>
>>
>>However your date format suggests that it is a DateTime2, then documentation says: 21 or 121 -- ODBC canonical (with milliseconds) default for time, date, datetime2, and datetimeoffset. -- yyyy-mm-dd hh:mi:ss.mmm(24h) . That makes it easier and you can use the code you tried (not tried?):
>>
>>

>>select * from wws_invoice where invdate like '2015-06-01%'
>>

>>
>>and get the invoice record. Here is a demo code:
>>
>>

>>DECLARE @myDates TABLE (myDate DATETIME2);
>>INSERT INTO @myDates (myDate)
>>VALUES
>>('2015-06-01 11:52:59.057'),
>>('2015-06-01 11:52:59.054'),
>>('2015-06-01 13:52:59.057'),
>>('2015-06-01 14:52:59.057');
>>
>>SELECT * FROM @myDates WHERE myDate LIKE '2015-06-01%';
>>SELECT * FROM @myDates WHERE myDate LIKE '2015-06-01 11%';
>>SELECT * FROM @myDates WHERE myDate LIKE '2015-06-01 11:52:59%';
>>SELECT * FROM @myDates WHERE myDate LIKE '2015-06-01 11:52:59.054%';
>>

>>
>>"The accepted way" - who accepted that way which kills index usage completely!!!
>>
>>Doing that as in the StackOverflow link you provided is an overkill! DateTimes have always been problematic in searches if you don't treat them as strings. I will add this to that SO link.
>>
>>>
>>>Huh? How would you have a LIKE operation on a DateTime work? Using like automatically will treat the date like a string?
>>>
>>>If I have a date like this in the DB:
>>>
>>>

>>>2015-06-01 11:52:59.057
>>>

>>>
>>>and you do a query like this:
>>>
>>>

>>>select * from wws_invoice where invdate like '2015-06-01%'
>>>

>>>
>>>you get no match.
>>>
>>>Unless I miss something here the only way you can get a valid date selection via string is by an exact match:
>>>
>>>

>>>select * from wws_invoice where invdate = '2015-06-01 11:52:59.057'
>>>

>>>
>>>The accepted way to do partial date queries is to use DatePart() in TSQL or you can use nasty code to CAST a date to a VARCHAR:
>>>
>>>https://stackoverflow.com/questions/1629050/sql-server-datetime-like-select
>>>
>>>
>>>+++ Rick ---