CODEWITHSUNDEEP
sqlplsql
MxLDevs
MxLDevs

Reputation: 19506

SQL between vs >= startdate and <= enddate

I'm writing some SQL queries in PL/SQL that require me to filter the records based on date. The field is a date/time type, but since I don't really care about the time I figured I'll just omit it from my where clause.

So I'm writing something like 

WHERE
  f.logdate between to_date('2011/01/01', 'yyyy/mm/dd') and
                    to_date('2011/01/31', 'yyyy/mm/dd')

To get all the records for january. I read that this is supposed to be equivalent to

WHERE
  f.logdate >= to_date('2011/01/01', 'yyyy/mm/dd') and
  f.logdate <= to_date('2011/01/31', 'yyyy/mm/dd')

But my final results are not what I expected: there are less records when I use the BETWEEN keyword than when I explicitly state the bounds. Is it because my assumption of what BETWEEN does is wrong?

EDIT: ah nvm, it appears that the date is not the issue. There was a subquery that I was using that was filtering its result set by date as well and was specifying date/time while I'm not.

Upvotes: 0

Views: 4523

Answers (2)

umlcat
umlcat

Reputation: 4143

Could you show the type of the "logdate" field (the sql create sentence could help) ?

In some databases the date type is actually a datetime field, so if you are looking for dates after "Jan 01 2011", you are really looking for dates after "Jan 01 2011 12:00:00 p.m.".

It may be your case.

Upvotes: 2

m4tt1mus
m4tt1mus

Reputation: 1641

if the time is set to 0:00 or something strange like that it wont work properly. 

The query retrieves the expected rows because the date values in the query and the datetime values stored in the RateChangeDate column have been specified without the time part of the date. When the time part is unspecified, it defaults to 12:00 A.M. Note that a row that contains a time part that is after 12:00 A.M. on 1998-0105 would not be returned by this query because it falls outside the range.

http://msdn.microsoft.com/en-us/library/ms187922.aspx

Upvotes: 1

Related Questions