CODEWITHSUNDEEP
sqloracle-databasetime
Alkey29
Alkey29

Reputation: 199

Fetch records based on timestamp and not day Oracle

So i have a field that has a date on it formatted

mm/dd/yyyy HH:MM:SS

I want to select records that are before a certain time. So mm/dd/yyyy is not considered only timestamp.

so i want to

select * from table where datefield < 5:00am

Upvotes: 0

Views: 923

Answers (2)

Siva
Siva

Reputation: 9101

May be this can help.

select * from table where  to_number(to_char( timestamp, 'HH24' )) < 5

Upvotes: 0

vav
vav

Reputation: 4694

There are many functions to work with dates/timestamps in oracle. Here is an example of 'extract':

select extract(hour from d), extract(minute from d) 
from
(select systimestamp as d from dual)

EXTRACT(HOURFROMD) EXTRACT(MINUTEFROMD)
------------------ --------------------
                14                   52

And just in case, there is a have artillery of TO_CHAR, TO_NUMBER, TO_DATE functions

TO_NUMBER(TO_CHAR(yourdate, 'HH24')) < 5

Also, it is not clear from your question what type of column you have if it is date/timestamp than my answer above should work for you. if it is VARCHAR2 or similar, you have a choice: 1. convert to date using TO_DATE(yourdate, 'your format') 2. use string functions like SUBSTRING to manually separate hours and minutes.

Upvotes: 2

Related Questions