Hi,
I looking for a help in converting the Timestamp to a string in the format YYYY-MM-DDTHH:MI:SS.000+0000
Background:
I have a column Opt_Out_Time defined as a string and that stores the values in '2020-06-03T04:25:31.767+0000' format.
From the application, I need to pass two inputs START_DATE and END_DATE and can only pass as in the DateTime (DD/MM/YYYY HH:MI:SS AM) and this format has to query the table and get the records existing between two dates by matching date in this format YYYY-MM-DDTHH:MI:SS.000+0000
DB: Oracle 12C
Incorporating sample as suggested by @frank-kulash
Create table TestTable
(id varchar2(255),
Opt_Out Number(1,0),
Opt_Out_Time varchar2(255)
);
insert into Testtable values('A1',1,'2020-06-03T04:25:31.767+0100');
insert into Testtable values('B1',1,'2020-07-12T04:25:31.723+0000');
insert into Testtable values('C1',1,'2020-08-03T04:25:31.888+0100');
insert into Testtable values('D1',1,'2020-09-24T04:30:31.123+0000');
insert into Testtable values('E1',1,'2020-10-03T04:22:31.543+0000');
insert into Testtable values('F1',1,'2020-06-04T04:13:31.223+0100');
insert into Testtable values('G1',1,'2020-06-05T04:12:31.321+0100');
insert into Testtable values('H1',1,'2020-06-06T04:20:31.979+0000');
My Query is:-
Select * from TestTable where Opt_Out_Time between '06/01/2020 00:10:00 AM' and '10/01/2020 11:50:00 PM';
Problem: How do i convert DD/MM/YYYY HH:MI:SS AM or YYYY-MM-DD HH24:MI:SS, so that I can query table?