Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback, please email oracle-forums_moderators_us@oracle.com

Finding TIMESTAMP rows

BeefStuNov 30 2022

I'm experimenting with finding a range of TIMESTAMPs. As you can see below I can successfully retrieve rows when I use the 'between" method.

When I try to use >= and < operators I'm getting a syntax error. My question is it possible to use the second method to find TIMESTAMP rows? If so, how can I get the second query to work?
Thanks in advance to all who respond and your expertise.

CREATE TABLE TABLE_NAME(
   ts TIMESTAMP 
);

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742000', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742001', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742002', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742003', 'YYYY-MM-DD HH24:MI:SS.FF'));

INSERT INTO TABLE_NAME (ts) VALUES (TO_TIMESTAMP('2022-11-30 06:14:00.742004', 'YYYY-MM-DD HH24:MI:SS.FF'));

SELECT *
  FROM TABLE_NAME 
 WHERE ts 
   BETWEEN TO_TIMESTAMP ('2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6')
       AND TO_TIMESTAMP ('2022-11-30 06:14:00.742003', 'yyyy-MM-dd HH24:mi:ss.ff6');

TS
30-NOV-22 06.14.00.742001 AM
30-NOV-22 06.14.00.742002 AM
30-NOV-22 06.14.00.742003 AM

Fingselect * from table_nane
 where ts >= 
'2022-11-30 06:14:00.742001', 'yyyy-MM-dd HH24:mi:ss.ff6' AND
ts < '2022-11-30 06:14:00.742004', 'yyyy-MM-dd HH24:mi:ss.ff6';
This post has been answered by Saubhik on Nov 30 2022
Jump to Answer
Comments
Post Details
Added on Nov 30 2022
6 comments
66 views