Simple Query Question

[Freddo]

Hi,

I thought I new well enough - but now I'm not so sure... Please de-mystify
this for me... (the actual questions are below my case study)...

In the query that follows the CAST uses smalldatetime which I believe is
wrong for the date being compared to because it includes milliseconds.
However, leaving that "as-is" and changing the ...AND runtype = 'E'... to
...AND runtype LIKE 'E'... I still get desired result (now rows are returned
when using the "=" operator). Changing the CAST to a datetime works in BOTH
cases as expected.

SELECT * FROM my_table
WHERE client = '123'
AND CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'
AND runtype ='E'

Q1: Why "do" I get results when the "=" is changed to "LIKE" in the query
even though I've incorrectly used CAST(... AS smalldatetime)?

Q2: Also - Why, if I remove: "client = '321' AND" from the query, use the
"=" operator *and* still CAST as a smalldatetime, do I still get the results
I expect?

I hope these are clear questions and examples. Thanks in advance to all of
you!

Regards - Freddo

[Scho]

Freddo,

First off it's always a good idea to check Books Online aka BOL for
syntax and using keywords if it's giving a dodgy result.
http://msdn.microsoft.com/en-us/library/ms179859.aspx

The like operator here is acting exactly like an =, you're searching
for a field that contains 'E' and a field like 'E' - the same in terms
of what you're telling the SQL query engine what you want it to do.
If you want anything with an E in it then you need to use wildcards on
either side, e.g. '%E%'. This will get you anything with an E in it
anywhere in the field, if you lose the wildcard from the start it
brings back anything that starts with E and so on. Any string can
replace the E here as well I might add but all can be found out using
the BOL link above.

A tip for dates also, you don't generally need to state the time
(unless you need to) but you can use '01 JAN 2010' and the SQL query
engine will recognise it as a datetime and run the query on it for
you. You can then add the time if needed onto that as well but I
personally find it's a lot quicker when doing ADHOC queries but this
will always depend on your data and your envrionment.
As for Q2, would need more info on the data really but if only client
123 has data at this time that's all you'll get! Depends on the
criteria, use a between for your date and see if that returns more
results and go from there.

HTH,
Scho

[Tom Cooper]

I have no idea why changing = to LIKE changes the result. It should not.
Could you provide sample DDL and data like that below that shows that? Also
which version of SQL server you are running.

For example when I run the following on either SQL 2005 or SQL 2008, in both
cases, the row is returned. That is the CORRECT result. The reason is that
'2009-12-08 16:50:07.227' is not a datetime. That's a string, so it's
datatype is char(23). So when you compare a char(23) to a smalldatetime,
data type precidence says to implicitly convert the char(23) to a smalldate
time. And the comparison of these two smalldatetimes is, of course, equal.

Create Table my_table (client varchar(10), run_date datetime, runtype
char(1));

Insert my_table(client, run_date, runtype)
Select '123', '2009-12-08 16:50:07.227', 'E'

SELECT * FROM my_table
WHERE client = '123'
AND CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'
AND runtype = 'E'

SELECT * FROM my_table
WHERE client = '123'
AND CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'

AND runtype Like 'E'

Drop Table my_table;

Tom

"Freddo" <Fre...@discussions.microsoft.com> wrote in message
news:6FA1E4FD-6212-4B25...@microsoft.com...

[Freddo]

Thanks Scho!

I did figure out that the "LIKE 'E'" and "= 'E'" were not the main problem
at all. It is the inclusion of the "date criteria" that is causing the issue
and my confusion.

The two following queries return the same results (casting AS smalldatetime
*or* datetime). The date string is the same for both queries:

select * from trand
where CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'

select * from trand
where CAST(run_date AS datetime) = '2009-12-08 16:50:07.227'

It appears that the CAST(run_date AS smalldatetime) will return rows when
compared to a string containing the milliseconds without a problem (I would
have thought it wouldn't - something implicit here?!?).

NOTE THIS! This is where I need to learn what's going on...

When the date criteria is used in conjunction with the 'runtype' column
(using "runtype LIKE 'E'" **or** "runtype = 'E'") I do not get results when
using the "=" operator. Here are the two queries. BTW - Every row in the
table has client = '123' but I still need to use filter in case other clients
are in the table in the future.

This does NOT return rows:

SELECT * FROM my_table
WHERE client = '123'
AND CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'
AND runtype = 'E'

This DOES return rows:

SELECT * FROM my_table
WHERE client = '123'
AND CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227'

AND runtype LIKE 'E'

So - what's happening here when the run_date criteria is used along with the
runtype criteria and the only difference is whether the query uses "LIKE" or
"=" ? That's what I'm not "seeing" clearly.

Thanks again to you and all...

Regards - Freddo

###
"Scho" wrote:

> .
>

[Freddo]

Thanks Tom.

Sorry - but not familiar with the acronym DDL. Please enlighten me.

Also - with your example I get the same results (SQL 2005) as you. But my
database acts differently. The SAME query works as I've initially stated
against my data. If I change smalldatetime to datetime I get results using
either = or LIKE as expected.

Regards - Freddo

###

"Tom Cooper" wrote:

> .
>

[Tom Cooper]

Sorry. DDL is "data definition language" - the Create Table, Create Index,
etc statements. What I'm really asking for is some Create Table, Create
Indexes, Insert statements etc that you can post that demonstrates this
problem, if that is possible. I realize it might not be.

It is possible that there is something wrong with your database. You can
check the integrity of your table and it's indexes with (if your table is
large, this might take a while, so I would run it when your server is not
busy):

DBCC CHECKTABLE (my_table) WITH DATA_PURITY

Tom

"Freddo" <Fre...@discussions.microsoft.com> wrote in message

news:0DEA5719-F1EC-4A44...@microsoft.com...

[Gert-Jan Strik]

Okay, let's start.

Your expression CAST(run_date AS smalldatetime) = '2009-12-08
16:50:07.227' might easily evaluate to true. This is because of data
type precedence. A smalldatetime has a high precedence than a literal
string. So the literal string is converted to a smalldatetime before the
comparison is executed. This can be proved with this query:

select CAST(getdate() AS smalldatetime)
union all
select '2009-12-08 16:50:07.227'

The result of this query shows no seconds or miliseconds for either
value.

As for the difference between "runtype = 'E'" and "runtype LIKE 'E'", I
would not focus on that. If the query is supposed to return values for
runtype = 'E' and it doesn't, then just investigate that.

Good luck.

--
Gert-Jan
SQL Server MVP

[Scho]

Freddo,

I have an idea with why only the LIKE gets you results; check the data
type.
I'm guessing it's not a CHAR(1) or VARCHAR(1) field, and it has spaces
inserted as well as the E character you're expecting.

So when we query runtype = 'E' we get zilch. When we do runtype = 'E '
we may get results as we add the space.
Run this and the copy and paste the result into the t-sql window but
between brackets, this should give away any spaces and where they are.

SELECT DISTINCT RunType FROM My_Table WHERE RunType LIKE '%E%'

I'd suspect that much of your trouble comes from not understanding the
data that you query, try to widen your query results and look at
you're data more and you'll find that you can get the results you need
with a quick investigation and a check of BOL if needed.

HTH,
Scho

[Freddo]

Thanks for clearing up the precidence actions, I was not aware of that
happening... If I may continue please...

It "appears" that the combination filtering the smalldatetime comparison
(CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227') *AND* the
runtype = 'E' (or using LIKE) causes the problem.

That's what had me baffled. The run_type is a CHAR(1) datatype column. When
these two filters are "AND"ed together - it returns rows when LIKE is used
but does not when "=" is used.

Any other ideas?!? Thanks again for clearing some of this up for me.

Regards - Freddo

"Gert-Jan Strik" wrote:

> .
>

[Gert-Jan Strik]

Freddo wrote:
>
> Thanks for clearing up the precidence actions, I was not aware of that
> happening... If I may continue please...
>
> It "appears" that the combination filtering the smalldatetime comparison
> (CAST(run_date AS smalldatetime) = '2009-12-08 16:50:07.227') *AND* the
> runtype = 'E' (or using LIKE) causes the problem.
>
> That's what had me baffled. The run_type is a CHAR(1) datatype column. When
> these two filters are "AND"ed together - it returns rows when LIKE is used
> but does not when "=" is used.
>
> Any other ideas?!? Thanks again for clearing some of this up for me.
>
> Regards - Freddo

The existence of one predicate should not affect the other predicate. So
your descriptino it sounds fishy.

First, make sure you have installed the latest service packs.

Second, make sure you do not have any data corruption. Tom Cooper posted
a usable command for that.

Third, I would check the data in "run_type" for the "incorrect" rows.
For those rows, run this query:

SELECT ASC(run_type) Value_of_RunType, ASC('E') AS Value_of_E
FROM my_table

If the values are not the same, then the character just looks like an E,
but is in fact a different character.

Then, if the problem still persists and is still unclear, then post back
at least the following information:
1. the SQL Server version, patch level and edition ("SELECT @@version")
2. the table definition, or at least the relevant parts of it (such as
the primary key and the columns you are querying against)
3. ALL indexes of the table. In this case it is essential to mention all
indexes that have column "runtype" or "run_date" in them
4. the complete (original) query that has or exposes the problem. So no
simplified version (unless that simpler version also exposes the
problem)

It would be nice to have some sample data too and/or the query plan, but
maybe that is too much to ask.

[Freddo]

Thanks to ALL OF YOU that pitched in on this one. It turned out to be that
the database was running on a SQL 2005 RTM (no SP's were installed). After
installing SP3 my issue was gone and the query worked as "I" and all of you
expected it would.

That siad - in throubleshooting this - I did learn more from each post so
thanks again.

God bless and Merry Christmas!

- Freddo

#############################

"Gert-Jan Strik" wrote:

> .
>

[Freddo]

"Scho" wrote:

> .
>