|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Select using parameteris 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 nospam@meatonconsulting.com wrote:
Show quote > I have a rather simple select statement that is driving me mad! Here Instead of converting the date, try this:> 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 > 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 Thank you so much!!!! I would have never gone that route, but it makes
perfect sense! Thanks again! 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 nospam@meatonconsulting.com wrote:
> That solution works great for the example I gave, but what if I wanted On the page that I linked to, there is an example of using DATEDIFF and > 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 > 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. 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! You got really close:
SELECT * FROM dbo.ONT_LDAP_LOG WHERE (Message LIKE '%' + @SearchString + '%') ML --- http://milambda.blogspot.com/  |
|||||||||||||||||||||||
Other interesting topics