Oracle date Between Query

Question

I am using oracle database  i want to execute one query to check the data between two dates   NAME               START DATE     -------------    -------------  Small Widget       15-JAN-10 04 25 32 000000 PM       Product 1          17-JAN-10 04 31 32 000000 PM      select   from  lt TABLENAME gt  where start date   BETWEEN  15-JAN-10  AND  17-JAN-10    But I dont get any results from above query  I think I have to use  like  and      But I dont know where to use them  Please throw some lights on this    thanks in advance

User · Answer

Date Between Query   SELECT       FROM emp     WHERE HIREDATE between to date  to char sysdate   yyyy       09 01    yyyy mm dd       AND to date  to char sysdate   yyyy     1     08 31    yyyy mm dd

User · Answer

You need to convert those to actual dates instead of strings  try this   SELECT   FROM  lt TABLENAME gt  WHERE start date BETWEEN TO DATE  2010-01-15   YYYY-MM-DD   AND TO DATE  2010-01-17    YYYY-MM-DD      Edited to deal with format as specified   SELECT   FROM  lt TABLENAME gt  WHERE start date BETWEEN TO DATE  15-JAN-10   DD-MON-YY   AND TO DATE  17-JAN-10   DD-MON-YY

User · Answer

Judging from your output it looks like you have defined START DATE as a timestamp   If it were a regular date Oracle would be able to handle the implicit conversion   But as it isn t you need to explicitly cast those strings to be dates   SQL gt  alter session set nls date format    dd-mon-yyyy hh24 mi ss    2     Session altered   SQL gt  SQL gt  select   from t23   2  where start date between  15-JAN-10  and  17-JAN-10    3     no rows selected  SQL gt  select   from t23   2  where start date between to date  15-JAN-10   and to date  17-JAN-10     3     WIDGET                          START DATE ------------------------------  ---------------------- Small Widget                    15-JAN-10 04 25 32 000      SQL gt     But we still only get one row   This is because START DATE has a time element   If we don t specify the time component Oracle defaults it to midnight   That is fine for the from side of the BETWEEN but not for the until side   SQL gt  select   from t23   2  where start date between to date  15-JAN-10      3                       and to date  17-JAN-10 23 59 59     4     WIDGET                          START DATE ------------------------------  ---------------------- Small Widget                    15-JAN-10 04 25 32 000 Product 1                       17-JAN-10 04 31 32 000  SQL gt    edit  If you cannot pass in the time component there are a couple of choices   One is to change the WHERE clause to remove the time element from the criteria    where trunc start date  between to date  15-JAN-10                                and to date  17-JAN-10     This might have an impact on performance  because it disqualifies any b-tree index on START DATE   You would need to build a function-based index instead    Alternatively you could add the time element to the date in your code   where start date between to date  15-JAN-10                         and to date  17-JAN-10      86399 86400     Because of these problems many people prefer to avoid the use of between by checking for date boundaries like this   where start date  gt   to date  15-JAN-10    and start date  lt  to date  18-JAN-10

User · Answer

As APC rightly pointed out  your start date column appears to be a TIMESTAMP but it could be a TIMESTAMP WITH LOCAL TIMEZONE or TIMESTAMP WITH TIMEZONE datatype too  These could well influence any queries you were doing on the data if your database server was in a different timezone to yourself  However  let s keep this simple and assume you are in the same timezone as your server   First  to give you the confidence  check that the start date is a TIMESTAMP data type    Use the SQLPlus DESCRIBE command  or the equivalent in your IDE  to verify this column is a TIMESTAMP data type    eg   DESCRIBE mytable  Should report    Name        Null  Type ----------- ----- ------------ NAME              VARHAR2 20   START DATE        TIMESTAMP   If it is reported as a Type   TIMESTAMP then you can query your date ranges with simplest TO TIMESTAMP date conversion  one which requires no argument  or picture      We use TO TIMESTAMP to ensure that any index on the START DATE column is considered by the optimizer   APC s answer also noted that a function based index could have been created on this column and that would influence the SQL predicate but we cannot comment on that in this query  If you want to know how to find out what indexes have been applied to table  post another question and we can answer that separately   So  assuming there is an index on start date  which is a TIMESTAMP datatype and you want the optimizer to consider it  your SQL would be     select   from mytable where start date between to timestamp  15-JAN-10   AND to timestamp  17-JAN-10    9999999     999999999 is very close to but isn t quite 1 so the conversion of 17-JAN-10 will be as close to midnight on that day as possible  therefore you query returns both rows    The database will see the BETWEEN as from 15-JAN-10 00 00 00 0000000 to 17-JAN-10 23 59 59 99999 and will therefore include all dates from 15th 16th and 17th Jan 2010 whatever the time component of the timestamp   Hope that helps     Dazzer

User · Answer

Following query also can be used   select      from t23  where trunc start date  between trunc to date  01 15 2010   mm dd yyyy    and trunc to date  01 17 2010   mm dd yyyy

[sql] Oracle date "Between" Query

Examples related to sql

Examples related to oracle

Examples related to oracle10g

Examples related to where-clause

Examples related to date-arithmetic