Capturing wrong manually entered dates without crashing

[senamuna]

Hi,

DBMS Oracle 9i, Reporting tool Crystal Reports XI

In my warehousing system, the DateTimes the trucks unload items are manually entered, in order to compare against other system generated DateTimes. But the dates are not always correctly entered in the proper format.

When I tried to use the Unloaded DateTime for reporting, the query crashes due to incorrect dates.

Is there a way to build the query such that, I can return incorrect Date values in case of errors OR if no errors, to run the query? I tried to anticipate the erroneous formats and to build query around it, but was wondering whether there is an easier and neater way.

Dates are supposed to enter as DD/MM/YYYY and time as HH24MI.

My basic query is like the following. Ref_Nbr_1 & Ref_Nbr_2 stores date & time respectively.

SELECT to_date((trim(ref_nbr_1) || trim(ref_nbr_2)),'dd/mm/yyyyhh24mi') In_Date_Time
FROM ASN_Header

I have read only privileges and cannot create any objects in the database.

Any advise/ comment is appreciated.

 

[SantaMufasa]

I have read only privileges and cannot create any objects in the database.

Despite your having READ ONLY access, I must presume that you can run anonymous PL/SQL blocks. Given that, you can trap exceptions such as bad-date values and handle them without crashing.


As an example, I have created a two-row "ASN_HEADER" table that contains a "bad date" and a "good date":

SQL> select * from asn_header;

EXAMPLE_NUMBER REF_NBR_1  REF_NBR_2
-------------- ---------- ---------
             1 01/13/2009 0723
             2 13/01/2009 0723

2 rows selected.

The row with the "bad date" has an identity of "EXAMPLE_NUMBER" = 1 and the row with the "good date" has an identity of "EXAMPLE_NUMBER" = 2.

In my code, below, I create a 16-character bind variable, "In_Date_Time" that represents "In_Date_Time" in your code, above.

Notice that the "bad date" value populates "In_Date_Time" with an error message (without crashing the code), while the "good date" value populates "In_Date_Time" with the character equivalent of the good date/time value (which I what I believe you wanted to pass to Crystal Reports, right?)

SQL> var In_Date_Time char(16)
SQL> declare
  2      hold_date date;
  3  begin
  4      :In_Date_Time := null;
  5      select to_date((trim(ref_nbr_1) || trim(ref_nbr_2)),'dd/mm/yyyyhh24mi')
  6        into hold_date
  7        from asn_header
  8       where example_number = 1;
  9      :In_Date_Time := to_char(hold_date,'dd/mm/yyyy hh24:mi');
 10  exception
 11      when others then
 12          :In_Date_time := 'Bad header Date.';
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select 'Header Date/Time = "'||:In_Date_Time||'"' results from dual;

RESULTS
-----------------------------------------------------
Header Date/Time = "Bad header Date."

1 row selected.

SQL> 
SQL> declare
  2      hold_date date;
  3  begin
  4      :In_Date_Time := null;
  5      select to_date((trim(ref_nbr_1) || trim(ref_nbr_2)),'dd/mm/yyyyhh24mi')
  6        into hold_date
  7        from asn_header
  8       where example_number = 2;
  9      :In_Date_Time := to_char(hold_date,'dd/mm/yyyy hh24:mi');
 10  exception
 11      when others then
 12          :In_Date_time := 'Bad header Date.';
 13  end;
 14  /

PL/SQL procedure successfully completed.

SQL> select 'Header Date/Time = "'||:In_Date_Time||'"' results from dual;

RESULTS
-----------------------------------------------------
Header Date/Time = "13/01/2009 07:23"

1 row selected.

Let us know if this gives you the insight you need to resolve your situation.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[senamuna]

Hi,

Thank you for the reply and my apologies for the late response.

I use TOAD and tried to run the modified script (I took out "where example_number = 2;" ). But it is asking for a value for :In_Date_Time and I provided a date. The script is running but how can I get the results out ? I assume that the results to be written to a text file. Is it possible to get output in a way that can be used as the data source query for Crystal Reports ?

Thank you again for the help.