How to get the minutes of a date field

Navigation
0

I am trying to update the records in an Oracle SQL database and I can not find the correct sentence.

The EXPENSES table is composed of the following fields:

DATE

TYPE: DATE

REGISTER EXAMPLE: 01/01/2018 12:00

SPENDING

TYPE: NUMBER

REGISTER EXAMPLE: 100

What I need to do is UPDATE the fields that have the minutes = 00, ie (12:00, 1:00, 2:00, etc ...) by X (1 for example) numerical value.

Sentence: 

UPDATE GASTOS
SET GASTO = 1 
WHERE TO_CHAR(FECHA);

I had researched on the subject and found that the sentence TO_CHAR had to be used, but I always receive an error, of invalid sentence.

Greetings, I am available for any questions.

    
asked by byte96 05.09.2018 в 17:58
source

3 answers

2

For what you are asking, you only have to add in the where the condition by which you will filter the records 

UPDATE GASTOS
SET GASTO = 1 
WHERE TO_CHAR(FECHA,'MI') = 0;

As it is a field of type date, extract can not be used, so the solution is to pause it with to_char and equalize the minutes that you want

    
answered by 05.09.2018 / 19:25
source
0

byte96 you can do the following 

SELECT to_char( sysdate, 'MI') 
FROM dual;

UPDATE GASTOS SET GASTO = (CASE WHEN '00' = (SELECT to_char( FECHA, 'MI') 
FROM gastos) THEN 1 END);

You must specify that you want to take out the date, since the date is 01/01/2018 12:00 and to_char takes what you specify ie 

TO_CHAR(sysdate, 'yyyy/mm/dd') --tomaria el año mes y dia de la fecha

please check here different formats and more in depth

  

to_char

    
answered by 05.09.2018 в 18:46
0

you could use the Oracle EXTRACT function: Here the documentation: link

Basically for your example you could raise the following Query: 

UPDATE GASTOS SET GASTO = 1
WHERE EXTRACT(MINUTE FROM FECHA) = 0;

This would update all records that have minutes in 0.

    
answered by 05.09.2018 в 19:05
Pass fields delimited by commas to several rows access 2013 Error with java exceptions