Monday, July 30, 2012

How to get data for a given range for TIMESTAMP in oracle 11g

Problem: To find data for a given two dates, whereas in database columns are TIMESTAMP.

Environment: Datbase 11g.

Use Case: In database, For a table datatype of a coulmn is TIMESTAMP instead of date. But, user need to find recodes based on given dates (Where time is not required).

Suppose for a Online Book Order system, we have a table to store order of books with following table struture.

CREATE TABLE book_order
  (
    order_num NUMBER,
    book_id   NUMBER,
    order_date TIMESTAMP(6)
  );

Insert few records:

INSERT INTO book_order VALUES (1,3001, '05/07/12 12:15:32');
INSERT INTO book_order VALUES (2,3001, '26/06/12 12:17:18');
INSERT INTO book_order VALUES (3,1021, '26/06/12 12:05:53');
INSERT INTO book_order VALUES (4,3051, '08/06/12 11:07:44');
INSERT INTO book_order VALUES (5,6703, '01/06/12 12:54:57');
INSERT INTO book_order VALUES (6,3401, '01/06/12 11:20:43');
INSERT INTO book_order VALUES (7,1606, '29/05/12 10:42:41');
INSERT INTO book_order VALUES (8,2986, '25/05/12 16:19:45');
INSERT INTO book_order VALUES (9,2986, '05/07/12 12:31:45');

COMMIT;

SELECT * FROM book_order;
-- 9 Rows Selected

SELECT * FROM book_order WHERE order_date BETWEEN '25/05/12' AND '05/07/12';
-- 7 rows selected (Expeted 9 Rows)

select * from book_order
WHERE order_date > '25/05/12'
  AND order_date   < '05/07/12';
-- 7 rows selected (Expeted 9 Rows)

SELECT *
FROM book_order
WHERE order_date BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- 7 rows selected (Expeted 9 Rows)

SELECT *
FROM book_order
WHERE to_date(order_date,'dd/mm/yy') BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- Error Message : ORA-01830: date format picture ends before converting entire input string

Here is our solution for which we are looking for:
SELECT *
FROM book_order
WHERE to_date(TO_CHAR(order_date,'dd/mm/yy')) BETWEEN to_date('25/05/12','dd/mm/yy') AND to_date('05/07/12','dd/mm/yy');
-- 9 rows selected

DROP TABLE book_order;

1 comment: