comparar fechas de diferentes tablas al detalle de los segundos

Question

Good evening,

I have a SP and I want to compare 2 dates from different tables, but in the form 'dd/mm/yyyy hh:mi:ss'

I am using to_char(date01,'dd/mm/yyyy hh:mi:ss')> to_char(date02,'dd/mm/yyyy hh:mi:ss')

but it throws me errors.

For example: if the date is 02/12/2016 07:40:12>02/02/2022 06:40:46

it indicates that it is true, and it is not, it is considering the day and not the entire date.

when I only use date01>date02, I have the problem you consider for example.

'02/15/2022 07:48:50'='02/15/2022 07:50:22' (only considers the date)

How can I compare date, minutes and seconds regardless of the server configuration.

Thank you,

PROCEDURE SPU_CUENTA
(
p_nro in varchar2,
   pr_Ret OUT number
) is
 vfecha varchar(100);
 vcount int;
begin


select COUNT(DFEC_SISTEMA) into vcount from TAB Where c=1;

IF vcount>0 THEN 
select to_char(DFEC_SISTEMA,'dd/mm/yyyy hh:mi:ss') into vfecha from TAB Where c=1;

   EXECUTE IMMEDIATE  'SELECT COUNT(DFEC_ANULA) FROM tablab WHERE to_char(DFEC_ANULA,'dd/mm/yyyy hh:mi:ss')>'''||vfecha||'''' into pr_Ret;
   
   END IF;

end;

Answer 1

Code you suggest would make sense if columns involved were VARCHAR2 (which is a bad idea; store dates into DATE datatype columns).

• If those columns really are DATEs, then part of your question (which suggests format) is meaningless - we compare dates as they are, simply by e.g. date1 > date2. Converting them to characters - in a format you specified - is plain wrong.

• If those columns are strings, then you'll have to convert them TO_DATE, not TO_CHAR

Procedure you wrote should be a function; they are designed to return a value. Yes, you can use a procedure, but - why would you? You can't use it in SQL (only in PL/SQL).

Besides, code can be heavily shortened/optimized, as you need just one select statement. You don't have to first check whether there any rows in tab that satisfy the condition, and then select some other info - use a subquery instead.

Finally, why are you using dynamic SQL? There's nothing dynamic in your code.

I'd suggest something like this, see if it makes sense.

FUNCTION spu_cuenta (p_nro IN VARCHAR2)
   RETURN NUMBER
IS
   pr_ret  NUMBER;
BEGIN
   SELECT COUNT (dfec_anula)
     INTO pr_ret
     FROM tablab
    WHERE dfec_anula > (SELECT dfec_sistema
                          FROM tab
                         WHERE c = 1);

   RETURN pr_ret;
END;