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