Problem with "like" statement

[hdmnp]

Hello all.

When I run the statement:

select regdate,description,machine_id,machine_nm,status from iris_log.dbo.tb_log_info where regdate like '%23%'

I get the result:
2009-01-23 08:05:06.000
Administration Software is Closed. Operator's Name:admin

But if I do:

select regdate,description,machine_id,machine_nm,status from iris_log.dbo.tb_log_info where regdate like '%-23%'

the result is null...

why?!?!

TIA,
-hdmnp

[kanaku]

Probably because the dates stored in your actual database are just that. Dates. (ie January 4, 2008; December 16, 1908; etc)

The dashes(-) you see when you retrieve results (ie SELECT date FROM tablename) are just 'formatting' or presentational characters. The same way you see pipes (|) in between columns. They aren't really part of the 'data' that gets stored in the column.

So a wildcard search for '%-%' wouldn't return anything significant. The same way searching for a pipe '%|%' in a column wouldn't return all rows.

(That's just a guess. I'm 90% sure about it)

[Ramy Mahrous]

If you need to use wildcard with date you need to cast it first to string, and then play with it.

[msi]

You can't work with datetime data type in the same manner as with character data types.
If you need what have asked, try this:
select regdate,description,machine_id,machine_nm,status from iris_log.dbo.tb_log_info where convert(varchar(10), regdate, 120) like '%-23%'

Functions Transact-SQL handling datetime data types

Ramy's and MSI's solutions work perfect if you have to use the LIKE clause, but DATETIME data types need to be handled differently. I would recommend not using the LIKE clause and going with the SQL function DATEPART(). If you need to retrieve records that occured on the 23rd, you can use this WHERE clause

SELECT * FROM iris_log.dbo.tb_log_info
WHERE DATEPART(Dd, regdate) = 23

But either methods work fine.