Return only the time in the Select

Question

Return only the time in the Select

Navigation

#1 by (1 votes)
#2 by (1 votes)
#3 by (0 votes)

2

How do I return only the time in Select?

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

The code looks 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);

oracle11g

asked by anonymous 29.12.2016 / 16:38

3 answers

What shortcut keys in PHPStorm to remove Namespaces that are not in use in a Class? Pass vector by reference or value?

score 1 · Answer 1

Elaine, I believe you do not need to create the field of horalocacao and horadevolucao , you can do as follows:

Based on the data passed, the creation table 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, if you wanted the fields of horalocacao and horadevolucao converted to hours, would look 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 DATE saves the date and time values, so you do not need to create the fields of horalocacao and horadevolucao , and if you wanted to display only the time or date you make through the query.

score 1 · Answer 2

1

So:

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

29.12.2016 / 16:46

score 0 · Answer 3

0

Try this:

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

29.12.2016 / 17:57