CODEWITHSUNDEEP
sqloracle-database
jackie21
jackie21

Reputation: 337

How to extract time in HH24:MM from varchar in Oracle

I have a column in the following varchar format. I would like to extract the time based on a condition e.g. < 7:00.

Table1
Column: timer(varchar)
23:45
05:00
07:00
22:00

Expected output
    test
    05:00
    07:30

I tried the following:

Select *
FROM Table1
where timer < 7:00

However, the result is not as expected.

Upvotes: 0

Views: 145

Answers (2)

Popeye
Popeye

Reputation: 35910

If this is just time and you want a proper comparison then you can convert them to date and compare them.

Select *
FROM Table1
where to_date(timer,'hh24:mi') < to_date('07:00','hh24:mi');

please note that your expected output contains 07:30 but it is not less than 07:00 so it will not be part of the output if you compare it with less than 07:00.

Cheers!!

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269913

Oracle does not have a time date, so presumably the type is a string.

Use string comparisons:

where time < '07:00'

Note that the leading 0 is important!

Upvotes: 2

Related Questions