CODEWITHSUNDEEP
oracle-database
Jayakumar
Jayakumar

Reputation: 13

Between not included the end date in oracle

I am writing the query like select * from tablename where date between '27-mar-2015' and '1-apr-2015'.

but records with date '1-apr-2015' is not retrieved from the oracle database date type is TIMESTAMP(6).

Upvotes: 2

Views: 2707

Answers (5)

Chaithra Suchith
Chaithra Suchith

Reputation: 1

I have faced same issue. Using TRUNC Date Function helped.

select * from tablename where trunc(date,'DAY') between '27-mar-2015' and '1-apr-2015'

Upvotes: 0

indra aditya
indra aditya

Reputation: 1

Try to use this code: 

select * from tablename where to_date(date,'DD-MON-YYYY')  between '27-mar-2015' and '1-apr-2015'.

Upvotes: -1

Lalit Kumar B
Lalit Kumar B

Reputation: 49112

between '27-mar-2015' and '1-apr-2015'

'27-mar-2015' is NOT a DATE, it is a string literal. You are forcing Oracle to do an implicit datatype conversion.

date type is TIMESTAMP(6)

Since your data type is TIMESTAMP, you need to take care of the precision of the TIMESTAMP datatype which holds fractions of a second to a precision between 0 and 9 decimal places, the default being 6.

BETWEEN 
   to_timestamp('27-mar-2015 01.01.01.000001','dd-mon-yyyy HH24:MI:SS.FF') 
AND
   to_timestamp('01-apr-2015 23.59.59.999999','dd-mon-yyyy HH24:MI:SS.FF')

For example,

SQL> ALTER SESSION SET nls_timestamp_tz_format = 'DD-MON-YYYY HH24:MI:SS.FF';

Session altered.

SQL> SELECT
  2    to_timestamp('27-mar-2015 01.01.01.000001','dd-mon-yyyy HH24:MI:SS.FF') st,
  3    to_timestamp('01-apr-2015 23.59.59.999999','dd-mon-yyyy HH24:MI:SS.FF') end
  4  FROM dual
  5  /

ST                                    END
----------------------------------    ----------------------------------
27-MAR-15 01.01.01.000001000 AM       01-APR-15 11.59.59.999999000 PM

SQL>

Upvotes: 4

piet.t
piet.t

Reputation: 11921

You have to note that timestamp-columns (and with oracle even date-columns) always include the time of day and that date '1-apr-2015' actually means '1-apr-2015 00:00:00' - then everything makes sense. The exact string-representation of timestamps might vary according to the configured locale.

To get date including the first of april you best use between ... and '2-apr-2015' if you don't mind having the first microsecond of that day included.

Upvotes: 1

starko
starko

Reputation: 1149

Try convert to_date:

select * from tablename 
where date  between to_Date('27-mar-2015','dd-mm-yyy') 
and to_date('1-apr-2015','dd-mm-yyyy')

Upvotes: 0

Related Questions