I want to ask If I want to add new column (time column) in report but I haven’t any permission to alter the creation table I need another method . Maybe we can separate the time from date time column or it’s impossible .
And I want to make the time count hourly not in second please .
This is the table which I have :
datetime | clientchannel | servicename | service_count |
---|---|---|---|
13_02_2022 9:35 | ***** | notification | 2 |
CodePudding user response:
Apply TO_CHAR
function to DATETIME
column with desired format mask, e.g.
select datetime,
to_char(datetime, 'hh24:mi') only_time, --> this
clientchannel, ...
from your_table
Presuming, of course, that DATETIME
column's datatype is DATE
. If not (which would be a shame and an error), you'd use SUBSTR
, e.g.
select substr(datetime, instr(datetime, ' ') 1) only_time
from ...
[EDIT] As you commented that you decided to store date value as a string (bad choice, once again) and that code I posted doesn't work, here's a demo which shows that it does:
SQL> create table temp (datetime varchar2(20));
Table created.
SQL> insert into temp values ('13_02_2022 9:35');
1 row created.
SQL> select substr(datetime, instr(datetime, ' ') 1) only_time
2 from temp;
ONLY_TIME
--------------------------------------------------------------------------------
9:35
SQL>
CodePudding user response:
select substr(datetime, instr(datetime, ' ') 1) only_time 2 from temp;
ONLY_TIME
9:35