Oracle timestamp sql query

Question

I have stored TIMESTAMP attribute in the database in the following format:
11/17/2014 3:00:00 PM
11/17/2014 4:00:00 PM
etc...
I am trying to query for all records ( I stored 30 days) where hour is 3:00 PM.

Select * from DaysTb where timeOfday =to_date('11/17/2014 3:00:00 PM','mm/dd/yyyy hh12:00')

however, my query is incorrect. Googling did not help me to find an answer. I would appreciate some guidance. Thanks.

Do you mean the timestamp attribute is stored as a STRING in the database? or as a true TIMESTAMP data type? If it is the latter (and I hope it is), it is NOT stored in the format you show - that is a string format, not a timestamp "format." Also, when you say 3:00 PM, do you mean exactly 3:00:00, or any time between 3:00:00 and 3:59:59? By the way, to find out the data type of your columns, you can try DESCRIBE DAYSTB if you use sqlplus (or Toad or SQL Developer). — user5683823
– user5683823, Commented Apr 22, 2016 at 15:42
The data are stored as TIMESTAMP. With my co-worker help, I used Extract with HOUR and was able to query all the records within a month where hour equals to 3:00 PM. — Chris
– Chris, Commented Apr 23, 2016 at 18:48

Chris · Accepted Answer · 2016-04-25 12:31:08Z

1

Here is the correct Oracle SQL statement

select *  from yourTableName where 
EXTRACT(HOUR FROM data_date ) = 15;

It returns all the records with 15:00 o'clock stamp.

edited Apr 25, 2016 at 12:31

answered Apr 23, 2016 at 18:49

Chris

291 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Oracle timestamp sql query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related