SQL Server datetime LIKE select?

Question

in MySQL

select * from record where register_date like '2009-10-10%'

What is the syntax in SQL Server?

Thank you.

Answer 1

I solved my problem that way. Thank you for suggestions for improvements. Example in C#.

string dd, mm, aa, trc, data;
dd = nData.Text.Substring(0, 2);
mm = nData.Text.Substring(3, 2);
aa = nData.Text.Substring(6, 4);
trc = "-";
data = aa + trc + mm + trc + dd;

"Select * From bdPedidos Where Data Like '%" + data + "%'";

Answer 2

You could use the DATEPART() function

SELECT * FROM record 
WHERE  (DATEPART(yy, register_date) = 2009
AND    DATEPART(mm, register_date) = 10
AND    DATEPART(dd, register_date) = 10)

I find this way easy to read, as it ignores the time component, and you don't have to use the next day's date to restrict your selection. You can go to greater or lesser granularity by adding extra clauses, using the appropriate DatePart code, e.g.

AND    DATEPART(hh, register_date) = 12)

to get records made between 12 and 1.

Consult the MSDN DATEPART docs for the full list of valid arguments.

Answer 3

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

Answer 4

You can also use convert to make the date searchable using LIKE. For example,

select convert(VARCHAR(40),create_date,121) , * from sys.objects where     convert(VARCHAR(40),create_date,121) LIKE '%17:34%'

Answer 5

Unfortunately, It is not possible to compare datetime towards varchar using 'LIKE' But the desired output is possible in another way.

    select * from record where datediff(dd,[record].[register_date],'2009-10-10')=0

Answer 6

You can use CONVERT to get the date in text form. If you convert it to a varchar(10), you can use = instead of like:

select *
from record
where CONVERT(VARCHAR(10),register_date,120) = '2009-10-10'

Or you can use an upper and lower boundary date, with the added advantage that it could make use of an index:

select *
from record
where '2009-10-10' <= register_date
and register_date < '2009-10-11'