0

How can I convert varchar2 (i.e. string) to timestamp?

The original data

08:00 AM - 06:00 PM
10:00 AM - 04:00 PM

so I can compare between them.

--STORE
CREATE TABLE STORE(
StoreID         VARCHAR2(10)  NOT NULL,
StoreAddress    VARCHAR2(50),
WeekDaysHours   VARCHAR2(20),
WeekendHours    VARCHAR2(20),
PRIMARY KEY(StoreID));

The values

INSERT INTO STORE VALUES ('S004', 'High Drive', '09:00 AM - 05:00 PM', '08:00 AM - 06:00 PM' );
INSERT INTO STORE VALUES ('S005', 'Snake Rd', '09:00 AM - 09:00 PM', '10:00 AM - 04:00 PM' );
Abra
  • 19,142
  • 7
  • 29
  • 41
JK1
  • 33
  • 5
  • What is your desired output, and what have you already tried? – pmdba May 17 '21 at 03:25
  • Does this answer your question? [Convert a string date into datetime in Oracle](https://stackoverflow.com/questions/7920637/convert-a-string-date-into-datetime-in-oracle) – Martheen May 17 '21 at 03:26
  • @pmdba as I mentioned I'm trying to compare between them , e.g. which one is earlier. I still couldn't find any similar example, all I found has date format with it which isn't part of the data. – JK1 May 17 '21 at 03:39
  • @Martheen It has date which is not part of the data – JK1 May 17 '21 at 03:42
  • Oracle does not have a "time" data type. When you say `8:00am - 06:00pm` does that mean the same day? Could they have occurred several days apart? You will have to make some assumptions about date: i.e. that both times occur on the same date, in order to compare them for order. Otherwise there's nothing to say which came before the other. For example 1-May-21 10:00pm comes after 1-May-21 8:00am, but before 2-May-21 08:00am. Without knowing the date component, I don't know how to calculate the interval between them. – pmdba May 17 '21 at 03:49
  • @pmdba it is the same day. the first store opens 08:00 AM and closes 06:00 PM, the other store opens 10:00 AM and closes 04:00 PM. I wanted to compare which one opens earlier. – JK1 May 17 '21 at 03:58
  • @pmdba I have edited the question – JK1 May 17 '21 at 04:05
  • Is it possible for the period to cross midnight? – Martheen May 17 '21 at 04:23
  • @Martheen I have edited the question with more info. can you pls check it? – JK1 May 17 '21 at 04:50
  • Maybe there is a good reason why the values in the examples you've found are `DATE` data types - it is the proper way of doing that. – Wernfried Domscheit May 17 '21 at 05:09
  • @WernfriedDomscheit I know but I'm not allowed to change the database that I have. the problem is each string has two times and want me to compare the first time of both of them. – JK1 May 17 '21 at 05:16

1 Answers1

2

If all values follow the same format and are valid (i.e. you don't have something like 08:BX FZ - #7-25 AM), then use substr (to extract each part of the string) and apply to_date function with appropriate format mask. Date portion will be "today"

[EDIT: no, it won't be "today". Quoting @mathguy's comment (thank you, @mathguy)]

Date portion will not be today. The defaults are "current year" and "current month", but the default "day of the month" is 1. The date portion will be the first day of the current month.

I'm modifying my session just to know what is what:

SQL> alter session set nls_date_format = 'dd.mm.yyyy hh24:mi';

Session altered.

SQL> with test (col) as
  2    (select '08:00 AM - 06:00 PM' from dual)
  3  select to_date(substr(col,  1, 8), 'hh:mi am') first_part,
  4         to_date(substr(col, 13, 8), 'hh:mi pm') second_part
  5  from test;

FIRST_PART       SECOND_PART
---------------- ----------------
01.05.2021 08:00 01.05.2021 18:00

SQL>

Now that these are dates, you can compare them.

Littlefoot
  • 131,892
  • 15
  • 35
  • 57
  • 2
    Date portion will not be today. The defaults are "current year" and "current month", but the default "day of the month" is 1. The date portion will be the first day of the current month. That doesn't change the answer though. –  May 17 '21 at 05:26
  • Thank you, @mathguy. I copy/pasted your comment into the answer. – Littlefoot May 17 '21 at 06:07