Return only the time in Select

Question

How do I return only the time in Select?

I declared the time as TIMESTAMP, in this case returned the date and time, but I want to return only the time. So I declared only DATE and now the time does not come, only the date comes.

the code is like this:

INSERT INTO locacao
  (loc_codigo
  ,cli_codigo
  ,vei_placa
  ,datalocacao
  ,horalocacao
  ,datadevolucao
  ,horadevolucao
  ,qtddias
  ,valordia
  ,finalizado)
VALUES
  (01
  ,1
  ,'ABC1234'
  ,(TO_DATE('01/02/2016','DD/MM/YYYY'))
  ,TO_DATE('08:30','HH24:MI:SS')
  ,TO_DATE('06/02/2016','DD/MM/YYYY')
  ,TO_DATE('07:00','HH24:MI:SS')
  ,5
  ,100.00
  ,1);

Answer 1

Thus:

SELECT TO_CHAR(datahora,'HH24') AS HORA FROM tabela

Answer 2

Elaine, I don’t think you need to create the horalocacao and horadevolucao, can do as follows:

Based on the data you passed, the table creation would look like this:

CREATE TABLE LOCACAO (loc_codigo number(11)
  ,cli_codigo number(11)   NOT NULL PRIMARY KEY
  ,vei_placa varchar(7)  NOT NULL
  ,datalocacao date  NOT NULL
  ,datadevolucao date  NOT NULL
  ,qtddias integer(11)
  ,valordia number(8,2)  NOT NULL
  ,finalizado number(3) DEFAULT(0) NOT NULL
  );

The Inclusion:

INSERT INTO locacao
  (loc_codigo
  ,cli_codigo
  ,vei_placa
  ,datalocacao
  ,datadevolucao
  ,qtddias
  ,valordia
  ,finalizado)
VALUES
  (01
  ,1
  ,'ABC1234'
  ,TO_DATE('01/02/2016 08:30:00','DD/MM/YYYY HH24:MI:SS')
  ,TO_DATE('06/02/2016 07:00:00','DD/MM/YYYY HH24:MI:SS')
  ,5
  ,100.00
  ,1);

And the query, in case you wanted the fields of horalocacao and horadevolucao converted to hours, would be like this:

SELECT loc_codigo
      ,cli_codigo
      ,vei_placa
      ,TO_CHAR(datalocacao,'DD/MM/RRRR') datalocacao
      ,TO_CHAR(datalocacao,'HH24:MI:SS') horalocacao
      ,TO_CHAR(datadevolucao,'DD/MM/RRRR') datadevolucao
      ,TO_CHAR(datadevolucao,'HH24:MI:SS') horadevolucao
      ,qtddias
      ,valordia
      ,finalizado
  FROM locacao

This solution would be better, because the DATE saves date and time values, so you don’t need to create the horalocacao and horadevolucao, and if wanted to display only the time or date makes through the consultation.

Answer 3

Try this:

SELECT 
  TO_CHAR(SYSDATE, 'HH24') Hora,
  TO_CHAR(SYSDATE, 'HH24:MM:SS') Completo
FROM dual;