SelectQuery

  • @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.)

  • 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

  • @Loggeddt is Used to filter the data

  • 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

  • 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

  • '2001-05-16 21:49:30.00' ->This format

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • If Enter the One Date(ie.LoggedDt) I want to display all the Records Belongs to LoggedDt....

  • 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

  • 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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • 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