How to convert varchar to datetime format and apply a condition-CodePudding

I have a ORACLE table where in one of the column the date time is stored as a text in the below format

 16:22:38:0570 14 SEP 2022

I would like to know how to parse this value as a proper datetime and apply a condition on SELECT.

Assume below is the table

CREATE TABLE MY_TEST_TABLE (
  DID number(10) NOT NULL,
  DTEXT varchar2 (50),
  CONSTRAINT id_pk PRIMARY KEY (DID)
);

Contains below records.

DID  DTEXT
1    13:25:58:0570 15 SEP 2022
2    10:20:38:0270 15 SEP 2022
3    16:22:38:0570 14 SEP 2022
4    02:19:18:0370 14 SEP 2022
5    03:29:14:0330 13 SEP 2022

How can I select the records that are older than or equal to 14th September 2022?

CodePudding user response：

You can use TO_TIMESTAMP() conversion with the following format mask

SELECT TO_TIMESTAMP(dtext,'HH24:MI:SS:FF DD MON YYYY','NLS_DATE_LANGUAGE=ENGLISH')
  FROM dual

Demo