none
How to use The BETWEEN operator to get between some date and current date (SQL)

    Question

  • When i put date in between  operator , The result will be between begin date and last date  but not include begin date and last date. 

    This may confuse you let me give an example 

    'Select something from table where  @date between  1/mm/yy and 5/mm/yy' :  the result will be in  2/mm/yy  - 4/mm/yy 

    And when I do 'Select something from table where  date between  1/mm/yy and 1/mm/yy' the result will be nothing -> 

    the result should be in  1/mm/yy

    How do i select result with  include begin date and last date ?

    Wednesday, September 11, 2013 6:41 PM

All replies

  • Between does exactly the same thing.

    Check the below example,

    declare @table table(datevalues date)
    insert into @table values('1/09/2013'),('2/09/2013'),('3/09/2013'),('4/09/2013'),('5/09/2013')
    select * from @table where datevalues between '1/09/2013' and '5/09/2013'
    select * from @table where datevalues between '1/09/2013' and '1/09/2013'

    Does the column you are trying to filter is having time part also? If yes, then you need to modify the query to consider the time part as well.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Wednesday, September 11, 2013 6:56 PM
  • Your question has little to do with database design; you should choose a forum more carefully.  TSQL would be a better choice. The short answer to your question can be found in the following discussion

    Tibor - datetime guide

    I suggest you bookmark and read the entire site when you can.  It is all very good information. I also suggest that you do some simple searching in the forums before posting your questions - you can benefit from past discussions, especially if they raise issues that you have not yet considered or encountered. 

    Wednesday, September 11, 2013 7:08 PM
  • For some help you may find this article I put today together useful

    http://social.technet.microsoft.com/wiki/contents/articles/19670.t-sql-useful-links.aspx

    It has a section on dates - you may want to check Tibor's article or a shorter Aaron Bertrand blog.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Thursday, September 12, 2013 1:37 AM
    Moderator
  • I moved the question, but I agree with the Sarat for sure. If you are not finding the value 1/mm/yy between 1/mm/yy and 1/mm/yy, there is something else going on. Please post sample data and query and unmark as answered if there more going on than you have asked please.

    Louis

    Without good requirements, my advice is only guesses. Please don't hold it against me if my answer answers my interpretation of your questions.

    Thursday, September 12, 2013 3:38 AM
    Moderator
  • The following blog deals with date range selections:

    http://www.sqlusa.com/bestpractices2008/between-dates/

    Datetime has the infamous "midnight bug" associated with it. Instead of a 24 hour day only the midnight records (rows) returned if not the correct datetime comparison predicates applied.


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012



    Friday, September 20, 2013 9:57 PM
    Moderator
  • First mistake: The only display format allowed in ANSI/ISO Standard SQL is ISO-8601, 'yyyy-mm-dd'; Microsoft is moving  to this standard with the new data types. You need to use the DATE data type. The BETWEEN predicate works correctly with DATE. 

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, September 20, 2013 10:19 PM
  • When i put date in between  operator , The result will be between begin date and last date  but not include begin date and last date. 


    Just a quick response: what you are stating above is not true. Because A BETWEEN B AND C is equivalent to A >= B AND A <= C. This is also true for dates.

    If you are not getting the results you are expecting, then you are probably using the datetime data type and are forgetting that they have a time portion, which is including in any comparison.


    Gert-Jan

    Saturday, September 21, 2013 9:50 AM