SQL Select using parameter

12 Sep 2006 2:29 PM

nospam

I have a rather simple select statement that is driving me mad! Here
is what currently works:

SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
FROM         dbo.ONT_LDAP_LOG
WHERE     (EntryType = 'Error') AND (CONVERT(varchar(10), EventTime,
101) LIKE '%8/22/2006%')

Here is what I would like to work:

SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
FROM         dbo.ONT_LDAP_LOG
WHERE     (EntryType = 'Error') AND (CONVERT(varchar(10), EventTime,
101) LIKE '%' & @Date & '%')

Basically, I have a parameter(@Date), but I need a percent sign before
and after since my date string looks like this: 8/22/2006 11:11:34 AM
I have tried everything, i.e %@Date%, '%@Date%' ...

I am assuming it is a simple answer. Also, the convert function is in
there just as part of my attempt to get this running. If it does not
need to be in there, that is fine. I am using this in SQL 2005 with
SQL Reporting Services

12 Sep 2006 2:38 PM

Tracy McKibben

nospam@meatonconsulting.com wrote:
Show quoteHide quote

> I have a rather simple select statement that is driving me mad! Here
> is what currently works:
>
> SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
> FROM         dbo.ONT_LDAP_LOG
> WHERE     (EntryType = 'Error') AND (CONVERT(varchar(10), EventTime,
> 101) LIKE '%8/22/2006%')
>
> Here is what I would like to work:
>
> SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
> FROM         dbo.ONT_LDAP_LOG
> WHERE     (EntryType = 'Error') AND (CONVERT(varchar(10), EventTime,
> 101) LIKE '%' & @Date & '%')
>
> Basically, I have a parameter(@Date), but I need a percent sign before
> and after since my date string looks like this: 8/22/2006 11:11:34 AM
> I have tried everything, i.e %@Date%, '%@Date%' ...
>
> I am assuming it is a simple answer. Also, the convert function is in
> there just as part of my attempt to get this running. If it does not
> need to be in there, that is fine. I am using this in SQL 2005 with
> SQL Reporting Services
>

Instead of converting the date, try this:

SELECT     EntiyId, Source, LDAP_DN, EntryType, Message, EventTime
FROM         dbo.ONT_LDAP_LOG
WHERE     EntryType = 'Error'
   AND DATEDIFF(day, EventTime, @Date) = 0

See
http://realsqlguy.com/serendipity/archives/5-No-Time-For-DATETIME-Values.html

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

12 Sep 2006 2:45 PM

nospam

Thank you so much!!!! I would have never gone that route, but it makes
perfect sense! Thanks again!

12 Sep 2006 3:17 PM

nospam

That solution works great for the example I gave, but what if I wanted
to run this:

SELECT * FROM dbo.ONT_LDAP_LOG
WHERE (Message LIKE '%sn has change from%') AND EventTime Between
@Start AND @End

I thought that was working fine, but it will only return a result for
8/31/2006 if @Start = 8/30/2006 and @End = 9/01/2006

12 Sep 2006 3:27 PM

Tracy McKibben

nospam@meatonconsulting.com wrote:

> That solution works great for the example I gave, but what if I wanted
> to run this:
>
> SELECT * FROM dbo.ONT_LDAP_LOG
> WHERE (Message LIKE '%sn has change from%') AND EventTime Between
> @Start AND @End
>
> I thought that was working fine, but it will only return a result for
> 8/31/2006 if @Start = 8/30/2006 and @End = 9/01/2006
>

On the page that I linked to, there is an example of using DATEDIFF and
DATEADD to "remove" the time portion of a DATETIME value. You should be
able to utilize that technique, just be sure to factor in the midnight
cutoff when determining your end date.

--
Tracy McKibben
MCDBA
http://www.realsqlguy.com

14 Sep 2006 8:40 PM

nospam

Thanks for helping me along this far. I have been able to create a ton
of reports. However, I have found a new need for my original question.
I want to search for specific text that a user enters, such as:

SELECT * FROM dbo.ONT_LDAP_LOG
WHERE (Message LIKE '%@SearchString%')

This fails unless I have the hard code the search string within percent
signs, which is useless. Thanks again for all help this far!

15 Sep 2006 7:54 AM

ML

You got really close:

SELECT * FROM dbo.ONT_LDAP_LOG
WHERE (Message LIKE '%' + @SearchString + '%')

ML

---
http://milambda.blogspot.com/