Find Data between a date range.

[richl796]

I'm trying to find all the records between to date/time entries. For example, all the records between yesterday at 2pm and today at 3PM. I'm using something like this but may not be even close:

select count(*) from foo where to_char(time_stamp, 'DD-MM-YY HH24:MI:SS') between
to_char('06-05-01 14:00:00', 'DD-MM-YY HH24:MI:SS') and
to_char('06-06-01 15:00:00', 'DD-MM-YY HH24:MI:SS');

[chrisrlong]

Try something more like:

select count(*) from foo where time_stamp between
to_date('06-05-2001 14:00:00', 'MM-DD-YYYY HH24:MI:SS') and
to_date('06-06-2001 15:00:00', 'MM-DD-YYYY HH24:MI:SS');

Remember - *never* touch the column side of the equation or you lose the possiblility of using an index.

...and never use 2-digit date formats - have we learned nothing from Y2K?

HTH,

- Chris

[Pinakin]

If the datatype of "TIME_STAMP" column in FOO table is DATE then there is no need for conversion at the time of comparision. Following should work :

SELECT COUNT(*) FROM foo WHERE time_stamp BETWEEN to_date('06-05-2001 14:00', 'DD-MM-RRRR HH24:MI') AND to_date ( '06-06-2001 15:00', 'DD-MM-RRRR HH24:MI') ;
-- OR :
SELECT COUNT(*) FROM foo WHERE time_stamp BETWEEN TO_DATE( TO_CHAR(TRUNC(SYSDATE)-1)||' 14:00', 'DD-MM-YY HH24:MI') AND TO_DATE(TO_CHAR(TRUNC(SYSDATE)||' 15:00', 'DD-MM-YY HH24:MI') ;

Do check the proper syntax.