Problem retrieving time from date field in oracle stored procedure

Question

I'm having problems retrieving the time from a date field in a SP. If I run the query:

select TO_CHAR(HOR_HST_ATN,'YYYY/MM/DD HH24:MI:SS') AS HOR_HST_ATN FROM AAM0_DT_RSTCN ;

It returns the date and time ok.

But in the SP, the code:

SELECT   FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN, 
    TO_DATE(HOR_DSD_ATN ,'DD/MM/YYYY HH24:MI:SS') as  HOR_DSD_ATN,
    TO_DATE(HOR_HST_ATN,'DD/MM/YYYY HH24:MI:SS') as HOR_HST_ATN

returns just the date ok but the time is 00:00

If I try the select in the SP with TO_CHAR as in:

SELECT   FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN,
 TO_CHAR(HOR_DSD_ATN ,'YYYY/MM/DD HH24:MI:SS') as  HOR_DSD_ATN,
 TO_CHAR(HOR_HST_ATN,'YYYY/MM/DD HH24:MI:SS') as HOR_HST_ATN

I get the error: SQLCODE: -6502 SQLERRM: ORA-06502: PL/SQL: error ...

Any clues ? BTW I must use a SP to retrieve this value.

Update: the column is type date. Here's the full SP:

FUNCTION FN_AAM_EV_RSTCN ( 

I_NUM_CONTRATO            IN  AAM0_DT_RSTCN.NUM_CONTRATO%TYPE,
I_COD_PFL                  IN  AAM0_DT_RSTCN.COD_PFL%TYPE, 
I_COD_APL_PFM              IN  AAM0_DT_RSTCN.COD_APL_PFM%TYPE, 
I_COD_PTO                  IN  AAM0_DT_RSTCN.COD_PTO%TYPE, 
I_COD_FNC                  IN  AAM0_DT_RSTCN.COD_FNC%TYPE
) RETURN BOOLEAN
AS
T_FEC_DSD_ATN                   AAM0_DT_RSTCN.FEC_DSD_ATN%TYPE;
T_HOR_DSD_ATN                   AAM0_DT_RSTCN.HOR_DSD_ATN%TYPE;
T_FEC_HST_ATN                   AAM0_DT_RSTCN.FEC_HST_ATN%TYPE;
T_HOR_HST_ATN                   AAM0_DT_RSTCN.HOR_HST_ATN%TYPE;
T_NOM_SEM_DSD_ATN               AAM0_DT_RSTCN.NOM_SEM_DSD_ATN%TYPE;
T_NOM_SEM_HST_ATN               AAM0_DT_RSTCN.NOM_SEM_HST_ATN%TYPE;

 O_RESULTSET2          REST_REFCUR;

BEGIN
 OPEN O_RESULTSET2 FOR 

   SELECT   FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN, 
    TO_CHAR(HOR_DSD_ATN ,'DD/MM/YYYY HH24:MI:SS') as  HOR_DSD_ATN,
    TO_CHAR(HOR_HST_ATN,'DD/MM/YYYY HH24:MI:SS') as HOR_HST_ATN
    FROM    AAM0_DT_RSTCN
    WHERE ROWNUM <=1
    AND   NUM_CONTRATO          = NVL ( I_NUM_CONTRATO, NUM_CONTRATO )
    AND     COD_PFL               = NVL ( I_COD_PFL, COD_PFL )
    AND     COD_APL_PFM           = NVL ( I_COD_APL_PFM, COD_APL_PFM )
    AND     COD_PTO               = NVL ( I_COD_PTO, COD_PTO )
    AND     COD_FNC               = NVL ( I_COD_FNC, COD_FNC )    
    AND     FLG_RCS               = 'D'
    AND     COD_TPO_CDC           = 'PC'
    ;

   FETCH O_RESULTSET2 INTO T_FEC_DSD_ATN, T_FEC_HST_ATN, T_NOM_SEM_DSD_ATN, T_NOM_SEM_HST_ATN, T_HOR_DSD_ATN, T_HOR_HST_ATN ;

  IF (O_RESULTSET2%NOTFOUND) THEN
    RETURN TRUE;
  ELSE
   dbms_output.put_line('EVALUO RESTRICCIONES: T_FEC_DSD_ATN '  || T_FEC_DSD_ATN || ' T_FEC_HST_ATN : ' || T_FEC_HST_ATN || ' T_NOM_SEM_DSD_ATN: ' ||
                 T_NOM_SEM_DSD_ATN || ' T_NOM_SEM_HST_ATN: '  || T_NOM_SEM_HST_ATN || ' T_HOR_DSD_ATN: ' || TO_CHAR(T_HOR_DSD_ATN ,'DD/MM/YYYY HH24:MI:SS')  || 
                ' T_HOR_HST_ATN: ' || TO_CHAR(T_HOR_HST_ATN ,'DD/MM/YYYY HH24:MI:SS') );

     IF (NOT((T_FEC_DSD_ATN IS NULL) and (T_FEC_HST_ATN IS NULL)) ) THEN

       IF ( NOT ((T_FEC_DSD_ATN  <= SYSDATE) AND (T_FEC_HST_ATN >= SYSDATE ))) THEN
                RETURN FALSE;
        END IF ;

    END IF ;

  RETURN TRUE;

  END IF ;

EXCEPTION

  WHEN NO_DATA_FOUND THEN
    RETURN TRUE; 

  WHEN OTHERS THEN


    IF O_RESULTSET2%ISOPEN THEN

      CLOSE O_RESULTSET2;

    END IF;


    DBMS_OUTPUT.PUT_LINE('ERROR en FN_AAM_EV_RSTCN : '); 
    DBMS_OUTPUT.PUT_LINE('  SQLCODE: ' || SQLCODE );
    DBMS_OUTPUT.PUT_LINE('  SQLERRM: ' || SQLERRM );

    ROLLBACK;

 END FN_AAM_EV_RSTCN;

Answer 1

Hi xain,

Since the TO_DATE function takes a VARCHAR as argument, you wouldn't use this function on a DATE field. If you want to retrieve both the date and the time from a DATE field you would just SELECT the fields without functions:

SELECT   FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN, 
         HOR_DSD_ATN, HOR_HST_ATN
   ...

update

The problem is that you are fetching TO_CHAR(HOR_DSD_ATN, 'DD/MM/YYYY HH24:MI:SS') into a DATE field. There is an implicit conversion that truncates your date. Use to_char on a DATE variable and use to_date on a CHAR variable.

My suggestion:

FUNCTION FN_AAM_EV_RSTCN(I_NUM_CONTRATO IN AAM0_DT_RSTCN.NUM_CONTRATO%TYPE,
                         I_COD_PFL      IN AAM0_DT_RSTCN.COD_PFL%TYPE,
                         I_COD_APL_PFM  IN AAM0_DT_RSTCN.COD_APL_PFM%TYPE,
                         I_COD_PTO      IN AAM0_DT_RSTCN.COD_PTO%TYPE,
                         I_COD_FNC      IN AAM0_DT_RSTCN.COD_FNC%TYPE) 
   RETURN BOOLEAN AS
BEGIN
   FOR cc IN (SELECT FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, 
                     NOM_SEM_HST_ATN, HOR_DSD_ATN, HOR_HST_ATN
                FROM AAM0_DT_RSTCN
               WHERE ROWNUM <= 1
                 AND NUM_CONTRATO = NVL(I_NUM_CONTRATO, NUM_CONTRATO)
                 AND COD_PFL = NVL(I_COD_PFL, COD_PFL)
                 AND COD_APL_PFM = NVL(I_COD_APL_PFM, COD_APL_PFM)
                 AND COD_PTO = NVL(I_COD_PTO, COD_PTO)
                 AND COD_FNC = NVL(I_COD_FNC, COD_FNC)
                 AND FLG_RCS = 'D'
                 AND COD_TPO_CDC = 'PC') LOOP
      dbms_output.put_line(' T_HOR_DSD_ATN: ' || 
                           TO_CHAR(cc.HOR_DSD_ATN, 'DD/MM/YYYY HH24:MI:SS') ||
                           ' T_HOR_HST_ATN: ' || 
                           TO_CHAR(cc.HOR_HST_ATN, 'DD/MM/YYYY HH24:MI:SS'));
      -- your logic with return boolean
   END LOOP;
   -- no rows found
   RETURN TRUE;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('ERROR en FN_AAM_EV_RSTCN : ');
      DBMS_OUTPUT.PUT_LINE('  SQLCODE: ' || SQLCODE);
      DBMS_OUTPUT.PUT_LINE('  SQLERRM: ' || SQLERRM);
      RAISE;
END FN_AAM_EV_RSTCN;

Answer 2

If HOR_HST_ATN is a date column then TO_DATE(HOR_HST_ATN,'DD/MM/YYYY HH24:MI:SS') must be a typo. You probably mean TO_CHAR().

Answer 3

If the columns were populated by dates without a time element Oracle defaults the tim eto midnight...

SQL> create table t23 (d date)
  2  /

Table created.

SQL> insert into t23 values (sysdate)
  2  /

1 row created.

SQL> insert into t23 values (to_date('03-FEB-2010', 'DD-MON-YYYY'))
  2  /

1 row created.

SQL> select to_char(d, 'DD/MM/YYYY HH24:MI:SS') as datetime from t23
  2  /

DATETIME
-------------------
02/02/2010 17:59:51
03/02/2010 00:00:00

SQL>

Answer 4

Your problem lies with the following code:

...
T_HOR_DSD_ATN                   AAM0_DT_RSTCN.HOR_DSD_ATN%TYPE;
T_HOR_HST_ATN                   AAM0_DT_RSTCN.HOR_HST_ATN%TYPE;
...
SELECT   FEC_DSD_ATN, FEC_HST_ATN, NOM_SEM_DSD_ATN, NOM_SEM_HST_ATN, 
TO_CHAR(HOR_DSD_ATN ,'DD/MM/YYYY HH24:MI:SS') as  HOR_DSD_ATN,
TO_CHAR(HOR_HST_ATN,'DD/MM/YYYY HH24:MI:SS') as HOR_HST_ATN
FROM    AAM0_DT_RSTCN
...
FETCH O_RESULTSET2 INTO T_FEC_DSD_ATN, T_FEC_HST_ATN, T_NOM_SEM_DSD_ATN,
T_NOM_SEM_HST_ATN, T_HOR_DSD_ATN, T_HOR_HST_ATN ;

Since the HOR_DSD_ATN and HOR_HST_ATN columns have the DATE data type, your local variables (T_HOR_DSD_ATN and T_HOR_HST_ATN) are also DATEs. However, in your SELECT you are converting these dates to strings with the TO_CHAR function. Therefore, your fetch is effectively doing this (I've made up some dates here):

T_HOR_DSD_ATN := '03/02/2010 09:33:30';
T_HOR_HST_ATN := '01/01/2010 12:30:00';

Since you are assigning a string to a date variable, Oracle must do an implicit TO_DATE - therefore it is using the session's NLS_DATE_FORMAT to convert them, which probably just gets the date portion (e.g. DD/MM/YYYY) and loses the time values.

To fix it, just remove the TO_CHAR()s from your SELECT statement - that way you will get dates + time values unmodified into your local variables.