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.
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.