SQL Server datetime LIKE select?

Try this

SELECT top 10 * from record WHERE IsActive = 1 
       and CONVERT(VARCHAR, register_date, 120) LIKE '2020-01%'

I am a little late to this thread but in fact there is direct support for the like operator in MS SQL server.

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'

you get that row.

However, this 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:

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%';

Doing datetime searches in SQL server without any conversion to string has always been problematic. Getting each date part is an overkill (which unlikely would use an index). Probably a better way when you don't use string conversion would be to use range checks. ie:

select * from record 
where register_date >= '20091010' and register_date < '20091011';

I realise this an old question, but a lot of the answers here don't give a SARGable answer here, nor cover parmetrisation.

First off, you are far better off using >= and < logic. For the date you want, then that would look like this:

SELECT {Your Columns}
FROM dbo.record
WHERE register_date >= '20091010'
  AND register_date < '20091011';

This'll include every time value on 2009-10-10, including the stroke of midnight on the day, and a nanosecond prior to 2009-10-11.

Often, however, you'll be parametrising your query, so instead what you can do is use DATEADD to add a day to the second clause:

DECLARE @DateParam date = '20091010';

SELECT {Your Columns}
FROM dbo.record
WHERE register_date >= @DateParam
  AND register_date < DATEADD(DAY,1,@DateParam);

This maintains SARGability and means that any indexes on register_date can be used.