CODEWITHSUNDEEP
sqlsql-server
ZZBeard
ZZBeard

Reputation: 29

SQL number Wildcard issue

I'm trying to add a wildcard to my date select query so i only pull the day not time. I.e. 2021-03-11 17:54:30.123. I thought a number could be substituted for a #.

select AID, LocalCoAltIn,LocalCoAltOut,EventTime
from EXCDS.dbo.WKS_LOG_VIEW
where EventTime like '2021-03-11 ##:##:##:###';

My query is returning no values even though they are in the table. Thanks.

Upvotes: 0

Views: 120

Answers (3)

Ivan Burakov
Ivan Burakov

Reputation: 1

Not sure that like operator would work for date as you want, but you still have few options. Use DATEPART function to retrieve year\month\etc and compare it with exact value that you need

select AID, LocalCoAltIn,LocalCoAltOut,EventTime from EXCDS.dbo.WKS_LOG_VIEW where DATEPART(year,EventTime) = 2021 AND DATEPART(month,EventTime) = 3 AND DATEPART(day,EventTime = 11);

Or use Gordon Linoff suggestion if you dont care about exact date part and only need to compare entire date without time

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522817

The LIKE operator in most flavors of SQL only support _ and * wildcards (matching any one single, or multiple characters). Gordon has given you a better approach, but if you wanted to fix your current query on SQL Server you could try:

SELECT AID, LocalCoAltIn, LocalCoAltOut, EventTime
FROM EXCDS.dbo.WKS_LOG_VIEW
WHERE EventTime LIKE '2021-03-11 [0-9][0-9]:[0-9][0-9]:[0-9][0-9]:[0-9][0-9][0-9]';

SQL Server extended the LIKE operator to accept a few extra things, such as character classes. Here [0-9] inside LIKE would match any single digit.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1271241

No! Don't use strings! One method is to convert to a date:

select AID, LocalCoAltIn,LocalCoAltOut,EventTime
from EXCDS.dbo.WKS_LOG_VIEW
where convert(date, EventTime) = '2021-03-11';

Another method is to use a range:

where EventTime >= '2021-03-11' and
      EventTime < '2021-03-12'

Upvotes: 2

Related Questions