May 16, 2011 at 5:21 am
@UserName nvarchar(32),
@LoggedDt datetime
AS
BEGIN
SELECT UserID,UserName,LoggedDt FROM X
WHERE (ISNULL(UserName,'''')) LIKE '%' + @UserName + '%'
OR(ISNULL(LoggedDt,'''')) LIKE '%' + @LoggedDt + '%'
ORDER BY UserName Desc
------------------------------------------------------------------------
I got this error....(Conversion failed when converting date and/or time from character string.)
May 16, 2011 at 5:46 am
Here is the culprit:
OR(ISNULL(LoggedDt,'''')) LIKE '%' + @LoggedDt + '%'
You should convert @LoggedDt to some string representation before concatenating to '%'.
However, this is not a good idea from a performance/data integrity standpoint.
Can you explain what you're after? How should the @LoggedDt parameter filter the data?
-- Gianluca Sartori
May 16, 2011 at 5:49 am
@Loggeddt is Used to filter the data
May 16, 2011 at 5:50 am
The values you are passing in, how are they generated ? Is yor date parameter being set/created as a string or datetime before being passed through ? If it is a string what is it's format ? Ie '2001-05-16 21:49:30.00'
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 16, 2011 at 5:54 am
vinaseetha87 (5/16/2011)
@Loggeddt is Used to filter the data
Hmmm. Yes, this was clear, but HOW are you filtering data?
Should the query return rows that match the date exactly or are you passing a sort of pattern? The question is why are you using LIKE in the date predicate?
-- Gianluca Sartori
May 16, 2011 at 5:54 am
'2001-05-16 21:49:30.00' ->This format
May 16, 2011 at 5:57 am
You're not giving enough info for anyone to help you. Explain what you are trying to do, we can't read your mind.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 6:00 am
If Enter the One Date(ie.LoggedDt) I want to display all the Records Belongs to LoggedDt....
May 16, 2011 at 6:03 am
As Gail has asked a little more info. If the string being passed in is in the format I suggested what does the data you are trying to match with look like ? Are you looking for exact matches ? Partial matches based on part of the date value passed ? Maybe within a date range ?
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
May 16, 2011 at 6:04 am
And LoggedDT will always be passed? Does it have a time or is it just a date? What about the column in the table?
Why are partial usernames getting passed?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 16, 2011 at 6:06 am
So you are only interested in values matching the date part and not worried about the time ? Ie all records entered today for instance ?
MCT
MCITP Database Admin 2008
MCITP Database Admin 2008
MCITP Database Dev 2008
www.jnrit.com.au/Blog.aspx
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply