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;
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;
This comment has been removed by the author.
ReplyDelete