converting numbers into Select Oracle hours format

Asked

Viewed 1,345 times

2

I need to make a point control report for my company, the situation is as follows. I have the table TGFPON recording the time stamp of the employees who have this desc.

desc TFPPON

ENTRADA     NOT NULL NUMBER(5)   
SAIDA       NOT NULL NUMBER(5)

When the record is stored in the INPUT column the numbers are like this: 1330 which in case would be 13:30. How can I make one select which displays the data in time format 13:30 ?

Thank you !

oracle

  • select substr( input, 1,2 ) || ':' || substr( input, 3,2 ) from table

2 answers

3


select REGEXP_REPLACE(lpad(to_char(ENTRADA),4,'0'), '([0-9]{2})([0-9]{2})', '\1:\2')
from TFPPON;
  • to_char - converts the number to text
  • lpad - adds 0 until completing 4 numbers for cases like 07:00 who would be saved as 700
  • REGEXP_REPLACE - format the code using regex

See example: http://sqlfiddle.com/#! 4/7feccd/8

  • It worked perfectly, thank you very much !

0

How can I make a select showing the data in the format of time 13:30?

As you already own the values existing in the bank and guarantor that they are only 4 numeric digits in the format of hh:mm and just want to return them with the time separator :, you can realize the query down below:

SELECT SUBSTR(ENTRADA, 0, 2) || ':' || SUBSTR(ENTRADA, 3, 2) AS ENTRADA, 
         SUBSTR(SAIDA, 0, 2) || ':' || SUBSTR(SAIDA, 3, 2) AS SAIDA
 FROM sua_tabela
  1. The command SUBSTR(entrada, posicaoo, [tamanho_entrada]), which returns us a slice of the input parameter, in this case the first 2 digits.
  2. The command || is responsible for concatenating strings in Oracle, but you can also use CONCAT.

Although it solves your problem, I advise working with data_type DATE since its goal is to work with the point control of your company, so it would be a safer and more complete way to work the points of your employees.

Work with date and time on oracle is easy and you can find several examples on the internet.

Browser other questions tagged oracle

You are not signed in. Login or sign up in order to post.