Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2011
    Posts
    6

    Red face Unanswered: How to get in Between records from a table

    Hi,
    I have a database table,I have two cloumns first is FromDate second is ToDate my requirement is i should be able to compare the records from these two columns with the current date.
    Example.
    SrNo FromDate ToDate Time
    1 9/09/2011 28/09/2011 19/09/2011 9:00 AM
    I want to compare that whether my current date is in between FromDate and ToDate
    For this im using this
    WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate
    but with this i also want to compare the current date of my computer with the Date in the Time column and if all the three conditions satisfies than it should return the result.But when im using Time to get Data its not retrieving and record.See Below Query
    WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate and Time=CURRENT_TIMESTAMP

    Please suggest.Its urgent

  2. #2
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Have you compared the data present in the Time column with the value of CURRENT_TIMESTAMP?

    Time column:
    19/09/2011 9:00 AM

    select CURRENT_TIMESTAMP
    2011-09-20 09:50:21.137

    You see the difference?
    The chance of those two to be equal is incredibly small. That is why you don't get any records in the result set.

    The smallest time slice in the Time column is minutes. The smallest time slice in CURRENT_TIMESTAMP is milliseconds. You should take that into account too.

    You can rewrite
    WHERE GETDATE() >= FromDate AND GETDATE() <= ToDate
    as
    WHERE GETDATE() BETWEEN FromDate AND ToDate

    GetDate() and CURRENT_TIMESTAMP are the same. You'd better stick to one notation, instead of switching between those two.

    Don't use reserved words for column, table, ... names, like "Time".
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •