0

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

5
Contributors
4
Replies
5
Views
8 Years
Discussion Span
Last Post by Geek-Master
0

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)

0

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.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.