• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 376
  • Last Modified:

SQL question between two dates doesn't include last date.

Hi I am trying to generate an SQL statement where the dates are between two ranges, but when I do them it works, but doesn't seem to include the last date.  

i.e. so If I do this

Select * from table where datefield between '01/01/2003' and '01/10/2003'

I get between 01/01/2003 to 01/09/2003, it is missing the 01/10/2003?  This is on a SQL server, should I be doing something like this?

Select * from table where datefield >= '01/01/2003' and datefield <= '01/10/2003'

Thanks in advance.
0
kesea
Asked:
kesea
1 Solution
 
keseaAuthor Commented:
In access I could do this to solve this, but SQL this throws an error.

Select * from table where datefield between #'01/01/2003'# and (#'01/10/2003'#+1)

Is there a way to do that in SQL?  That will answer my question as well.
0
 
Harisha M GCommented:
Hi kesea,
    Have you tried IN instead of BETWEEN?

Bye
---
Harish
0
 
JesterTooCommented:
You need to force the "time" portion of the ending date to "end of day" like this so it includes all of the records on the last date...

    Select * from table where datefield >= '01/01/2003' and datefield <= '01/10/2003 23:59:59:998'

HTH,
Lynn
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
JesterTooCommented:
Oops, that last colon should haveen a decimal point...

    Select * from table where datefield >= '01/01/2003' and datefield <= '01/10/2003 23:59:59.998'
0
 
rettiseertCommented:
Does your date field has time information?

If so then you could try something like:

Select * from table where datefield between #01/01/2003# and #01/10/2003 12:59:59 PM#
(that's for Access, I dont know if works for SQL server just changing # to ' )
0
 
rettiseertCommented:
a little late...
0
 
cachedVBCommented:
Hi kesea,
Are you sure that you get between 01/01/2003 and 01/09/2003 on the first query? From past experiences I have noted that between is excluding of the terms searched.  So yes, the second query should be used.

cachedVB
0
 
PePiCommented:
cachedVB is correct. Using BETWEEN gets the records between the 2 values inclusively. Are you sure you have a record that has a date = 01/10/2003?
0
 
JesterTooCommented:
Using either of the following queries will return the same rowset...

    Select * from table where datefield >= '01/01/2003' and datefield <= '01/10/2003 23:59:59:998'
or
    Select * from table where datefield between '01/01/2003' and  '01/10/2003 23:59:59:998'

When you specify a date literal without a time component SQL Server 2000 assumes it to be "00:00:00.000" (before the first microsecond of the day has passed).  Due to problems of precision there are some values of the final ".xxx" that aren't capable of being stored such as ".999".  The highest value of microsedonds that can be stored is ".998"... anything greater than that is the next date.

The true problem with the original query is that it only includes rows up through 01/10/2003 00:00:00.000 which misses any rows with dates of 01/20/2003 and times later in the day.  Other DBMS's recognize this problem (as does SQL Server 2005 when it is released) of only supporting a datetime combo datatype by also supporting just a date datatype and a time datatype individually.  Not only is the separate column types much easier to work with when you have no need for the other component, it saves space and execution time as well.

HTH,
Lynn
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now