3 Replies Latest reply on Aug 31, 2012 5:36 PM by BillArms_GS

    Variable substitution in Unit Testing validation - Date format?

      Using SQL Developer unit testing, I am having difficulty comparing Date datatype in a validation.

      I use a Dynamic Value Query to set the parameters of an insertion procedure under test.
      select * from (select doc_nbr as p_mstr_doc_nbr, doc_nbr as p_assoc_doc_nbr, sysdate as p_date_added from documents sample(0.01) order by dbms_random.random)  where rownum <= 1;
      So P_DATE_ADDED is a DATE datatype.

      I wish to validate that the insertion was correct. So I run a Process Validation returning one or more rows.
          AND to_char(DATE_ADDED,'dd-MON-yy HH24:MI:SS')  = {P_DATE_ADDED};
      I understand that the {P_DATE_ADDED} is substituting in a string. From Help page :

      "What is actually substituted is the string representation of the parameter value. For example:

      If P1 is a parameter of type NUMBER and has the value 2.1, the string {P1} will be replaced by the string 2.1.

      If P1 is a parameter of type VARCHAR2 and has the value ABC, the string '{P1}' will be replaced by the string 'ABC'. (Note the single-quotation marks around {P1} in this example.)"

      I need to know the format that the date is cast into as a string. Then I can do the comparision on the where clause.

      Any answers? or details of where is more in depth documentation?

      Thank you,