Professional Web Applications Themes

BETWEEN Function with Dates - Microsoft SQL / MS SQL Server

I have a query created to pull results based on a date range the user keys in. Right now, I am using the BETWEEN function, however, it seems to not return the complete result set. Problem appears to be with records that are equal to the dates keyed in. Example: Select * from test where date between '05/01/2003' and '05/30/2003' ID Amount Date 1 100 05/01/2003 2 200 05/20/2003 3 300 05/25/2003 4 400 05/30/2003 RESULTS: 2 200 05/20/2003 3 300 05/25/2003 What I need is all 4 records. Is there a way to make the BETWEEN function pull dates ...

  1. #1

    Default BETWEEN Function with Dates

    I have a query created to pull results based on a date range the user keys
    in. Right now, I am using the BETWEEN function, however, it seems to not
    return the complete result set. Problem appears to be with records that are
    equal to the dates keyed in.

    Example:

    Select * from test where date between '05/01/2003' and '05/30/2003'

    ID Amount Date
    1 100 05/01/2003
    2 200 05/20/2003
    3 300 05/25/2003
    4 400 05/30/2003

    RESULTS:
    2 200 05/20/2003
    3 300 05/25/2003

    What I need is all 4 records. Is there a way to make the BETWEEN function
    pull dates equal to and between the dates keyed in?

    Thanks in advance for the help.


    Scott Guest

  2. #2

    Default Re: BETWEEN Function with Dates

    Do you have a time portion which is <> 00:00:00 on the actual values? I first suggest you use
    the "safe" unseparated format to make sue that SQL Server interprets the parts correctly (or
    execute appropriate SET statement first). Then make sure you handle the time portion, something
    like:

    date > '20030501' AND date <= '20030531'

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Scott" <smorgansiuins.com> wrote in message news:O62oFXIRDHA.2276TK2MSFTNGP12.phx.gbl...
    > I have a query created to pull results based on a date range the user keys
    > in. Right now, I am using the BETWEEN function, however, it seems to not
    > return the complete result set. Problem appears to be with records that are
    > equal to the dates keyed in.
    >
    > Example:
    >
    > Select * from test where date between '05/01/2003' and '05/30/2003'
    >
    > ID Amount Date
    > 1 100 05/01/2003
    > 2 200 05/20/2003
    > 3 300 05/25/2003
    > 4 400 05/30/2003
    >
    > RESULTS:
    > 2 200 05/20/2003
    > 3 300 05/25/2003
    >
    > What I need is all 4 records. Is there a way to make the BETWEEN function
    > pull dates equal to and between the dates keyed in?
    >
    > Thanks in advance for the help.
    >
    >

    Tibor Karaszi Guest

  3. #3

    Default Re: BETWEEN Function with Dates

    The column is a date time type - so do I need to add the time in there as
    well?
    "Anith Sen" <anithbizdatasolutions.com> wrote in message
    news:%23sVUpcIRDHA.3880tk2msftngp13.phx.gbl...
    > That is strange; BETWEEN is inclusive of the boundary values and hence
    based
    > on the data you have shown it should bring back all the 4 rows. What is
    the
    > datatype of the Date column? Is there a time portion associated with the
    > data? Also try : WHERE datecol >= begdate AND datecol <= enddate
    >
    > --
    > - Anith
    > ( Please reply to newsgroups only )
    >
    >

    Scott Guest

  4. #4

    Default Re: BETWEEN Function with Dates

    The type of the column is datetime - do I need to add the time to the date
    to make sure it pulls the correct results?
    "Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszicorner stone.se>
    wrote in message news:OvIhddIRDHA.2460TK2MSFTNGP10.phx.gbl...
    > Do you have a time portion which is <> 00:00:00 on the actual values? I
    first suggest you use
    > the "safe" unseparated format to make sue that SQL Server interprets the
    parts correctly (or
    > execute appropriate SET statement first). Then make sure you handle the
    time portion, something
    > like:
    >
    > date > '20030501' AND date <= '20030531'
    >
    > --
    > Tibor Karaszi, SQL Server MVP
    > Archive at:
    [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]
    >
    >
    > "Scott" <smorgansiuins.com> wrote in message
    news:O62oFXIRDHA.2276TK2MSFTNGP12.phx.gbl...
    > > I have a query created to pull results based on a date range the user
    keys
    > > in. Right now, I am using the BETWEEN function, however, it seems to
    not
    > > return the complete result set. Problem appears to be with records that
    are
    > > equal to the dates keyed in.
    > >
    > > Example:
    > >
    > > Select * from test where date between '05/01/2003' and '05/30/2003'
    > >
    > > ID Amount Date
    > > 1 100 05/01/2003
    > > 2 200 05/20/2003
    > > 3 300 05/25/2003
    > > 4 400 05/30/2003
    > >
    > > RESULTS:
    > > 2 200 05/20/2003
    > > 3 300 05/25/2003
    > >
    > > What I need is all 4 records. Is there a way to make the BETWEEN
    function
    > > pull dates equal to and between the dates keyed in?
    > >
    > > Thanks in advance for the help.
    > >
    > >
    >
    >

    Scott Guest

  5. #5

    Default Re: BETWEEN Function with Dates

    You should use YYYYMMDD format when passing datetime criteria to avoid
    ambiguity; however, that is not the problem here.

    Is it a datetime column? If so, I can't explain why the first row isn't
    being returned. If you're storing times as well as dates in this column, I
    can explain why the 4th row is not being returned.

    If I run this script:
    select 1 as ID,100 As Amount,cast('20030501' as datetime) As testDate into
    #test
    union
    select 2,200,cast('20030520' as datetime)
    union
    select 3,300,cast('20030525' as datetime)
    union
    select 4,400,cast('20030530' as datetime)

    select * from #test where testdate between '20030501' and '20030530'

    I get all 4 rows returned, as expected.

    If I run this script:
    select 1 as ID,100 As Amount,cast('20030501 05:00' as datetime) As testDate
    into #test2
    union
    select 2,200,cast('20030520' as datetime)
    union
    select 3,300,cast('20030525' as datetime)
    union
    select 4,400,cast('20030530 12:30' as datetime)

    select * from #test2 where testdate between '20030501' and '20030530'

    I only get the first 3 records. This is expected, because '20030530 12:30'
    is larger than '20030530' (which defaults to '20030530 00:00' when being
    converted to datetime)

    To get all 4 records with the second set of data, you can use either of
    these techniques:
    select * from #test2 where testdate between '20030501' and '20030530
    23:59:59'
    or, as many prefer:
    select * from #test2 where testdate >='20030501' and testdate < '20030531'

    If you have an index on testdate, and you want the index to be used, DON'T
    use this technique:
    select * from #test2 where convert(char(8),testdate,112) between '20030501'
    and '20030530'
    Using a function on the criterion column makes the condition "nonsargable",
    which prevents an index from being used if it would have been. If there is
    no index on this column, then it does not hurt to use this technique.

    HTH,
    Bob Barrows

    PS. Hopefully you aren't using "date" as your column name: it's a reserved
    word which should be avoided when naming objects.

    Scott wrote:
    > I have a query created to pull results based on a date range the user
    > keys in. Right now, I am using the BETWEEN function, however, it
    > seems to not return the complete result set. Problem appears to be
    > with records that are equal to the dates keyed in.
    >
    > Example:
    >
    > Select * from test where date between '05/01/2003' and '05/30/2003'
    >
    > ID Amount Date
    > 1 100 05/01/2003
    > 2 200 05/20/2003
    > 3 300 05/25/2003
    > 4 400 05/30/2003
    >
    > RESULTS:
    > 2 200 05/20/2003
    > 3 300 05/25/2003
    >
    > What I need is all 4 records. Is there a way to make the BETWEEN
    > function pull dates equal to and between the dates keyed in?
    >
    > Thanks in advance for the help.

    Bob Barrows Guest

  6. #6

    Default Re: BETWEEN Function with Dates

    > date > '20030501' AND date <= '20030531'

    Actually I would use:

    [date] >= '20030501' AND [date] < '20030531'

    Yours (as written, surely not as intended) will miss any rows exactly at
    midnight on the 1st, and will miss all rows *except* those exactly at
    midnight on the 30th.

    (Strange that the query gets 30 days of May, but not the whole month,
    though...)


    Aaron Bertrand - MVP Guest

  7. #7

    Default Re: BETWEEN Function with Dates

    Thanks so much - I believe it was the time piece of the date time field that
    was causing the problem.
    "Bob Barrows" <reb_01501> wrote in message
    news:u74jsoIRDHA.1564TK2MSFTNGP12.phx.gbl...
    > You should use YYYYMMDD format when passing datetime criteria to avoid
    > ambiguity; however, that is not the problem here.
    >
    > Is it a datetime column? If so, I can't explain why the first row isn't
    > being returned. If you're storing times as well as dates in this column, I
    > can explain why the 4th row is not being returned.
    >
    > If I run this script:
    > select 1 as ID,100 As Amount,cast('20030501' as datetime) As testDate into
    > #test
    > union
    > select 2,200,cast('20030520' as datetime)
    > union
    > select 3,300,cast('20030525' as datetime)
    > union
    > select 4,400,cast('20030530' as datetime)
    >
    > select * from #test where testdate between '20030501' and '20030530'
    >
    > I get all 4 rows returned, as expected.
    >
    > If I run this script:
    > select 1 as ID,100 As Amount,cast('20030501 05:00' as datetime) As
    testDate
    > into #test2
    > union
    > select 2,200,cast('20030520' as datetime)
    > union
    > select 3,300,cast('20030525' as datetime)
    > union
    > select 4,400,cast('20030530 12:30' as datetime)
    >
    > select * from #test2 where testdate between '20030501' and '20030530'
    >
    > I only get the first 3 records. This is expected, because '20030530 12:30'
    > is larger than '20030530' (which defaults to '20030530 00:00' when being
    > converted to datetime)
    >
    > To get all 4 records with the second set of data, you can use either of
    > these techniques:
    > select * from #test2 where testdate between '20030501' and '20030530
    > 23:59:59'
    > or, as many prefer:
    > select * from #test2 where testdate >='20030501' and testdate < '20030531'
    >
    > If you have an index on testdate, and you want the index to be used, DON'T
    > use this technique:
    > select * from #test2 where convert(char(8),testdate,112) between
    '20030501'
    > and '20030530'
    > Using a function on the criterion column makes the condition
    "nonsargable",
    > which prevents an index from being used if it would have been. If there is
    > no index on this column, then it does not hurt to use this technique.
    >
    > HTH,
    > Bob Barrows
    >
    > PS. Hopefully you aren't using "date" as your column name: it's a reserved
    > word which should be avoided when naming objects.
    >
    > Scott wrote:
    > > I have a query created to pull results based on a date range the user
    > > keys in. Right now, I am using the BETWEEN function, however, it
    > > seems to not return the complete result set. Problem appears to be
    > > with records that are equal to the dates keyed in.
    > >
    > > Example:
    > >
    > > Select * from test where date between '05/01/2003' and '05/30/2003'
    > >
    > > ID Amount Date
    > > 1 100 05/01/2003
    > > 2 200 05/20/2003
    > > 3 300 05/25/2003
    > > 4 400 05/30/2003
    > >
    > > RESULTS:
    > > 2 200 05/20/2003
    > > 3 300 05/25/2003
    > >
    > > What I need is all 4 records. Is there a way to make the BETWEEN
    > > function pull dates equal to and between the dates keyed in?
    > >
    > > Thanks in advance for the help.
    >
    >

    Scott Guest

  8. #8

    Default Re: BETWEEN Function with Dates

    See Bob's reply. He elaborated pretty much (from a quick read) on my thoughts.

    --
    Tibor Karaszi, SQL Server MVP
    Archive at: [url]http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver[/url]


    "Scott" <smorgansiuins.com> wrote in message news:%23UoGoiIRDHA.3664tk2msftngp13.phx.gbl...
    > The type of the column is datetime - do I need to add the time to the date
    > to make sure it pulls the correct results?

    Tibor Karaszi Guest

Similar Threads

  1. Between Dates
    By javman in forum Coldfusion - Advanced Techniques
    Replies: 5
    Last Post: June 7th, 02:50 PM
  2. Dates & SQL
    By smokin_joe in forum Coldfusion Database Access
    Replies: 1
    Last Post: July 29th, 04:56 PM
  3. ASP/VBS Dates Between Dates
    By Sanjay in forum Dreamweaver AppDev
    Replies: 3
    Last Post: April 28th, 06:34 PM
  4. Dates again
    By Clive Moss in forum ASP Database
    Replies: 4
    Last Post: January 14th, 03:35 AM
  5. Help with Dates please
    By Ian Piper in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 7th, 07:55 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139