No Result set with a WHERE Clause.

  • In the query below, If omit the WHERE Clause, I get a record set. If I include the WHERE clause, I get no result set.

    The Dates I have in the query below do exist in the tblSRSalesRecording Table in the format as I have it in the query below.

    This has stunned me! Why does the WHERE clause return no records?

    SELECT A.AFSUserFirstName, A.AFSUserSurname, B.TimeStampEntered, A.LanUserID, B.UserEntered

    FROM tblAFSUser A INNER JOIN tblSRSalesRecording B ON A.LanUserId = B.UserEntered

    WHERE B.TimeStampEntered >= '2004-12-14' AND B.TimeStampEntered <= '2004-12-15'


    Kindest Regards,

  • What happens when you explicitely CAST your date as DATETIMES?

    Or when you write WHERE... >= '20041214'....?

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • Put SET DATEFORMAT YMD at the beginning of your procedure

  • Even better is to use a language independant format.

    Here are some very good thoughts by SQL Server MVP Tibor Karaszi on this topic: http://www.karaszi.com/sqlserver/info_datetime.asp

    --
    Frank Kalis
    Microsoft SQL Server MVP
    Webmaster: http://www.insidesql.org/blogs
    My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

  • I'm not sure what you mean by "What happens when you explicitely CAST your date as DATETIMES?" The B.TimeStampEntered Column is already a DATETIME DatType. Here is a sample for this column, 2001-07-04 14:15:39.633

    And the SET DATEFORMAT to YMD did not achieve anyting either.

    This has really blown me away. There must be something else werong but don't know where to look.


    Kindest Regards,

  • I have sorted out this issue. It was a data quality problem between the UserEntered column and the LanUserId column ob the JOIN.

    Thanks to all for your help.


    Kindest Regards,

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply