LIKE query on datetime 1

[TheConeHead]

How can I do something like:

select * from table where dattimeField like '%1/12/2005%'

I have tried this but it does not seem to work? Is there no way to do a LIKE on a datetime field?

 

[checkai]

select * from table where 
convert(char(10),dattimeField,101) ='01/12/2005'

try this...

 

[TheConeHead]

beauty - just a couple chages:

select * from table where
convert(char(10),dattimeField,101) LIKE '%01/12/2005%'

works great!

 

[SQLBill]

Why have the wildcard before and after the Date?

DATETIME does not have a value before the date. The only additional value would be the time at the end. But you convert the DATETIME to get rid of the time. So you are only using 10 characters of the DATETIME (01/12/2005 is 10 characters), so there CAN'T be any leading or trailing characters.

Finally, using the wildcards when they aren't needed will eliminate the use of any index on the DATETIME field.

-SQLBill

Posting advice: FAQ481-4875

 

[checkai]

did you try it without the like? if you didn't get everything maybe use this...

select * from table where 
convert(char(10),dattimeField,101) ='01/12/2005'
OR
convert(char(10),dattimeField,101) ='1/12/2005'

just because like statements aren't the quickest...

 

[TheConeHead]

yeah - tried it without and it did not work...

 

[r937]

this is much faster --

where dateadd(d,datediff(d,0,dattimeField),0)
       ='2005-01-12'

see thread183-972119

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (course starts January 9 2005)

 

[ESquared]

And I think this is faster than all the solutions presented so far:

WHERE
   DateTimeField >='2005-01-12' AND DateTimeField < '2005-01-13'

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k