CODEWITHSUNDEEP
sqloracle-database
Coopernicus
Coopernicus

Reputation: 95

How do I remove the date from a datetime stamp in Oracle?

Very quick question, if I have a field, for example closed_date, which has a datetime, i.e. 

01-JAN-19 09.00.00.000000000

And I would like to only look at cases closed between 09:00am and 10:00am each day, whats the syntax? How do I trunc the field just to time and only pull through cases which were closed each day between the 9am and 10am?

Many thanks

Upvotes: 1

Views: 1590

Answers (4)

q4za4
q4za4

Reputation: 652

select regexp_Replace('01-JAN-19 09.00.00.000000000','\d+-\w+-\d+ ') from dual

But if You want to search between hours, try EXTRACT, f.e.:

SELECT *
FROM   your_table
WHERE  to_char(your_column,
               'yyyy-mm-dd hh24:mi:ss.ff a.m.',
               'nls_date_language=american') in (9,10)

Updated answer. without nls_Date_language parameter, hour 9 o' clock could be also 9.pm and that is 21.

Upvotes: -1

Alex Poole
Alex Poole

Reputation: 191245

You can look at the hour portion alone; as you have a timestamp you can extract the hour number:

where extract(hour from closed_date) = 9

That will find rows there time is on or after 09:00:00, and before (not including) 10:00:00.

Usually when looking at data in a time period like 'between 9 and 10' you don't actually want to include the upper limit, because if you were also looking at data 'between 10 and 11' then data at exactly 10:00:00 would be included in both, which probably isn't what was intended. So it's common for date/time comparisons to use >= and < instead of between; you could also do this with a string comparison which you might consider clearer:

where to_char(closed_date, 'HH24:MI:SS') >= '09:00:00'
and to_char(closed_date, 'HH24:MI:SS') < '10:00:00'

or slightly more simply

where to_char(closed_date, 'HH24') >= '09'
and to_char(closed_date, 'HH24') < '10'

which in this case, as it's a single hour, is the same as:

where to_char(closed_date, 'HH24') = '09'

but then as you are only looking at the hour part anyway, extracting that as a number simplifies it even more (IMO).

Upvotes: 2

Chrᴉz remembers Monica
Chrᴉz remembers Monica

Reputation: 1904

You can use the extract function from oracle

SELECT EXTRACT(HOUR FROM timestamp_col_value) AS CURRENT_HOUR
FROM DUAL;

Note that the extract function only works in certain combinations of MONTH/DAY/HOUR and date types. See here for more details.

If you want to extract a hour from a datetime, you need to convert it to a timestamp first, i.e.

SELECT EXTRACT(HOUR FROM CAST(SYSDATE AS TIMESTAMP)) AS CURRENT_HOUR 
FROM DUAL;

Upvotes: 0

GMB
GMB

Reputation: 222432

You seem to be looking for a condition to filter a timestamp column based on its hour part. There are various ways to extract date parts from a timestamp, here is a solution that uses TO_CHAR :

TO_CHAR(closed_date, 'hh24') = '09'

This will match on timestamps whose time is higher than or equal to 9 AM and strictly smaller than 10 AM.

Upvotes: 1

Related Questions