Post reply

Date comparision in SELECT query

  • October 8, 2008 at 6:35 am

    #199128

    Hi All,

    I have the foll issue ....

    Please help me in solving this

    Create table Emp (EmpID INT, EmpName Varchar(20), DOJ Datetime)

    Go

    Insert Emp Select 1,'Ravi','2008-01-01'

    Insert Emp Select 2, 'Ram', '2007-01-01'

    Go

    Declare @WeekEnding DateTime

    Set @WeekEnding = '2008-01-01'

    Select * from Emp where DOJ= @WeekEnding

    The records are not retrieved for the date compared. Only Null result set is retrieved.

    Any help, Greatly appreciated.....

    Nit

    Regards
    Priya

  • October 8, 2008 at 6:47 am

    #882221

    by me, it returns the first employee record.

    are you running it from SSMS or from application?

    .

  • October 8, 2008 at 6:50 am

    #882225

    I run it from SSMS.....

    Regards
    Priya

  • October 8, 2008 at 7:03 am

    #882231

    I get the same result as Jacob. If you run the query without the WHERE clause, what do the date formats look like?

    --SJT--

  • October 8, 2008 at 7:08 am

    #882238

    It looks in the foll. format

    2008-01-01 00:00:00.000 (in the table)

    Regards
    Priya

  • October 8, 2008 at 7:20 am

    #882244

    Let's try putting in something other than Jan 1 in there... it's not clear from that select whether the first 1 is the month or the first 1 is the day. Maybe put in February 14 instead. Also, what's the value in the dateformat column returned by this statement?

    EXEC sp_helplanguage @@LANGUAGE

    For the Gurus,

    I'm wondering if there may be something under the hood in the database's dateformat.

    --SJT--

  • October 8, 2008 at 7:52 am

    #882271

    Try removing the dashes from the value in variable @WeekEnding:

    Set @WeekEnding = '20080101'

    This is the general notation of dates as a string that will convert to correct datetime values.

    And what are the results if you change the '=' to '>=' or '<=' in you WHERE clause?

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **

  • October 8, 2008 at 8:11 am

    #882303

    Hi!

    Thank u so much....

    I got the result.... I was wondered to see the date format set previously....

    Now it works.....

    Nit :hehe:

    Regards
    Priya

Viewing 8 posts - 1 through 7 (of 7 total)

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