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.
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%'
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
Last edited by peter_budo; Mar 6th, 2009 at 8:58 am. Reason: Keep It Organized - For easy readability, always wrap programming code within posts in [code] (code blocks) and [icode] (inline code) tags.
No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Unsolved 
