Like with the %

Question

I am trying to isolate the records returned to just from today. I am using the like 'XXX%' but it returns no records at all. What am i doing wrong here?

select CREATE_DATE from TELEGRAM_LOG tl with (nolock)
where tl.TELEGRAM_TYPE = 'ARQ'
and tl.DESCRIPTION like '%tuId=M180613969]'
and tl.TELEGRAM like 'ARQ100180423001%'
and tl.CREATE_DATE like  '2020-08-12%'

Are you sure there is a record that meet all the conditions? — RSax, Aug 13 '20 at 00:07
If you change the last line to "and tl.CREATE_DATE > dateadd(day, datediff(day, 0, getdate()),0)" does it work? — RSax, Aug 13 '20 at 00:10
Does this answer your question? [SQL Server datetime LIKE select?](https://stackoverflow.com/questions/1629050/sql-server-datetime-like-select) — Dai, Aug 13 '20 at 00:41

Dai · Answer 1 · 2020-08-13T21:48:10.433

In my answer, I assume tl.CREATE_DATE is a datetime (or datetime2 or datetimeoffset) column and not a date column.

Your SQL isn't working because the LIKE operator is for text (strings, varchar, etc) not dates.

For dates, use the normal <= and >= operators. You cannot "pattern match" dates like you can with strings - depending on your RDBMS you'll need to use either the EXTRACT function, or the individual YEAR, MONTH, DAY scalar functions to compare date components as numbers.

I recommend just using comparison operators, so change your LIKE predicate clause to this instead:

(
    tl.CREATE_DATE >= '2020-08-12 00:00:00'
    AND
    tl.CREATE_DATE <  '2020-08-13 00:00:00'
)

You don't need the time-of-day portion of the date-literals, but I prefer to put them in because it makes it clear to the reader that we're dealing with datetime values, not date values.

The above predicate can also be expressed as:

(
    tl.CREATE_DATE > '2020-08-12'
    AND
    tl.CREATE_DATE < '2020-08-13'
)

...but if the reader doesn't already know that tl.CREATE_DATE is not a date value then the semantics of this query are different (which why I'm not a fan of SQL in its present form).

If you do want to "pattern match" dates then you'd use the date-component functions (or EXTRACT):

(
    YEAR( tl.CREATE_DATE ) = 2020
    AND
    MONTH( tl.CREATE_DATE ) = 8
    AND
    DAY( tl.CREATE_DATE ) = 12
)

Another approach is to use the CAST or CONVERT function to change the datetime/datetime2/datetimeoffset value to a date value, as this will truncate the time-of-day portion, so comparisons will only use the date component, like so:

(
    CONVERT( date, tl.CREATE_DATE ) = '2020-08-12'
)

Alternatively to all of the above (and as a variation on my initial recommended answer) you could also do it like this:

(
    tl.CREATE_DATE >= '2020-08-12 00:00:00'
    AND
    tl.CREATE_DATE <= '2020-08-12 23:59:59'
)

That said, I'm not a fan of this approach because it potentially excludes events occurring in the 999ms in-between 2020-08-12 23:59:59.001 and 2020-08-13 23:59:59.999 (inclusive), as well as leap-seconds.

score 0 · Answer 2 · answered Aug 13 '20 at 00:08

0

You can use GETDATE() or current_timestamp.

where convert(varchar(10), t1.CREATE_DATE, 102) = convert(varchar(10), getdate(), 102)

answered Aug 13 '20 at 00:08

Ranjita Shetty

597
5
8

Applying `convert()` to `create_date` prevents the use of indexes and should thus be avoided. Instead inequality operations should be used like @Dai's answer demonstrates. – sticky bit Aug 13 '20 at 01:25

Like with the %

2 Answers2