Correct date range in SQL

This has gotten me a little paranoid, but I'm retrieving a set of records that fall within a period of time, say, the period from the january 1, 2011 (starting at midnight) to march 31, 2011 (all records up to 11:59:59 PM)

I'm using the condition

t.logtime between to_date('2011-01-01', 'yyyy-mm-dd') and to_date('2011-03-31')

Note that logtime is a datetime field.

Does this reflect what I want? Or am I actually missing 24 hours less a second? I could specify the time as well, but I was hoping I this could be 开发者_Go百科done without it.

Yes, you are missing nearly all of the last day. There are various solutions; probablt the simplest is:

t.logtime >= to_date('2011-01-01', 'yyyy-mm-dd')
and t.logtime < to_date('2011-04-01', 'yyyy-mm-dd')

I'd use the ANSI date literal syntax too:

t.logtime >= date '2011-01-01'
and t.logtime < date '2011-04-01'

Another way is:

trunc(t.logtime) between date '2011-01-01' and date '2011-03-31'

but note that that can no longer use an index on logtime (though it can use an index on trunc(logtime)).