SQL : how to find max time where its type is varchar2

Question

I have a question in which I have to find details of the store from a store table which opens the latest during the weekend. in store table, I have an attribute named weekendHours with the type varchar2 and the example is : (10:00 am - 5:00pm). I tried To_char( S.WeekendHours=max(S.WeekendHours ); but it does not work!! how can I do that?

Answer 1

If you want the store that opens the latest, then you conceivably could do:

select t.*
from t
order by t.weekendhours desc
fetch first 1 row only;

However, with your time format, that will not work, because 2:00 a.m. would be after 1:00 p.m.

So, you need to convert the range to a time. Here is one method:

select t.*
from t
order by cast(substring(t.weekendhours, 1, 8) as time) desc
fetch first 1 row only;

Of course, these queries use common methods across many databases. The logic might work on your database, but the syntax could be different.

Note: All of this is because the column weekendhours is broken. It should be in two columns, the weekend_opentime and weekend_closetime. Then the query would be trivial.

Answer 2

You can use a regular expression to extract the opening and closing times, and then manipulate those as needed:

SELECT s.*,
       TRIM(REGEXP_SUBSTR(s.weekend_hours, '[0-9]+:*[0-9]* *(am|pm)*', 1, 1)) AS OPENING_HOUR_STRING,
       TRIM(REGEXP_SUBSTR(s.weekend_hours, '[0-9]+:*[0-9]* *(am|pm)*', 1, 2)) AS CLOSING_HOUR_STRING
      FROM STORE s

Once you have the basic information above, you can manipulate it to obtain the opening and closing times as dates, then use MAX on the appropriate field:

WITH cteSTORE_STRING_VALUES AS
       (SELECT s.*,
               TRIM(REGEXP_SUBSTR(s.weekend_hours, '[0-9]+:*[0-9]* *(am|pm)*', 1, 1)) AS OPENING_HOUR_STRING,
               TRIM(REGEXP_SUBSTR(s.weekend_hours, '[0-9]+:*[0-9]* *(am|pm)*', 1, 2)) AS CLOSING_HOUR_STRING
          FROM STORE s),
     cteALL_STORE_VALUES AS
       (SELECT s.*,
               CASE
                 WHEN LENGTH(OPENING_HOUR_STRING) < 4 THEN LPAD(OPENING_HOUR_STRING, 4, '0')
                 ELSE OPENING_HOUR_STRING
               END AS PADDED_OPENING_STRING,
               CASE
                 WHEN LENGTH(CLOSING_HOUR_STRING) < 4 THEN LPAD(CLOSING_HOUR_STRING, 4, '0')
                 ELSE CLOSING_HOUR_STRING
               END AS PADDED_CLOSING_STRING
          FROM cteSTORE_STRING_VALUES s),
     cteSTORES_WITH_HOURS AS
       (SELECT a.*,
               CASE
                 WHEN UPPER(SUBSTR(a.OPENING_HOUR_STRING, -3)) IN (' AM', ' PM') THEN
                   TO_DATE(a.OPENING_HOUR_STRING, 'HH:MI AM')
                 WHEN UPPER(SUBSTR(a.OPENING_HOUR_STRING, -2)) IN ('AM', 'PM') THEN
                   TO_DATE(a.OPENING_HOUR_STRING, 'HH:MIAM')
                 WHEN INSTR(a.OPENING_HOUR_STRING, ':') > 0 THEN
                   TO_DATE(a.OPENING_HOUR_STRING, 'HH24:MI')
                 ELSE
                   TO_DATE(a.PADDED_OPENING_STRING, 'HH24MI')
               END AS OPENING_HOURS,
               CASE
                 WHEN UPPER(SUBSTR(a.CLOSING_HOUR_STRING, -3)) IN (' AM', ' PM') THEN
                   TO_DATE(a.CLOSING_HOUR_STRING, 'HH:MI AM')
                 WHEN UPPER(SUBSTR(a.CLOSING_HOUR_STRING, -2)) IN ('AM', 'PM') THEN
                   TO_DATE(a.CLOSING_HOUR_STRING, 'HH:MIAM')
                 WHEN INSTR(a.CLOSING_HOUR_STRING, ':') > 0 THEN
                   TO_DATE(a.CLOSING_HOUR_STRING, 'HH24:MI')
                 ELSE
                   TO_DATE(a.PADDED_CLOSING_STRING, 'HH24MI')
               END AS CLOSING_HOURS
          FROM cteALL_STORE_VALUES a)
SELECT *
  FROM cteSTORES_WITH_HOURS
  WHERE OPENING_HOURS = (SELECT MAX(OPENING_HOURS)
                           FROM cteSTORES_WITH_HOURS);

SQLFiddle here

Best of luck.