Problem getting rows between dates in SQL DEVELOPER

Question

I'm trying to get a row result from a table. I created a stored procedure IN SQL DEVELOPER where I can filter between 2 dates

i have the next DATE in my table

FECHACARGA - 17/NOV/2020

In the sp im trying to compare 2 strings that comes like this:

fechaIni IN VARCHAR2, fechaFin IN VARCHAR2,

I make this to get the row that matches.

SELECT COUNT(1)  
       INTO V_TOTAL
FROM Z_DOMI2_IB_SERVICE_STATUS
WHERE CLIENTE = clientId AND
      TIPOARCHIVO = tipoDeArchivo AND 
      ESTADO = estadoArchivo AND
     TO_CHAR(FECHACARGA, 'DD-MM-YYYY') BETWEEN TO_CHAR(TO_DATE(fechaIni, 'DD-MM-YYYY'), 'DD-MM-YYYY')
                                          AND  TO_CHAR(TO_DATE(fechaFin, 'DD-MM-YYYY'), 'DD-MM-YYYY');

This doesn't work, this gives me nothing just blank.

Thanks!

please edit the question, show the exact table DLL and the exact table data — OldProgrammer
– OldProgrammer, Commented Nov 18, 2020 at 19:43
You really should store your dates in proper date data type columns — jarlh
– jarlh, Commented Nov 18, 2020 at 19:43

MT0 · Accepted Answer · 2020-11-18 20:00:01Z

1

Don't use TO_CHAR, just use the DATE values:

SELECT COUNT(1)  
INTO   V_TOTAL
FROM   Z_DOMI2_IB_SERVICE_STATUS
WHERE  CLIENTE = clientId
AND    TIPOARCHIVO = tipoDeArchivo
AND    ESTADO = estadoArchivo
AND    FECHACARGA BETWEEN TO_DATE(fechaIni, 'DD-MM-YYYY')
                      AND TO_DATE(fechaFin, 'DD-MM-YYYY');

answered Nov 18, 2020 at 20:00

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Problem getting rows between dates in SQL DEVELOPER

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related