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
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)
If you need to use wildcard with date you need to cast it first to string, and then play with it.
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
SELECT * FROM iris_log.dbo.tb_log_info
WHERE DATEPART(Dd, regdate) = 23But either methods work fine.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.