CODEWITHSUNDEEP
sqloracle-database
great77
great77

Reputation: 143

Date filtering to extract from view not working

I HAVE A CODE THAT CONVERT A DATE FORMAT TO DD/MM/YYYY to create a view INITIALLY THE DATE FORMAT was dd.mm.yyyy hh24.mi.ss . so I converted it. 

  NVL(TO_CHAR(D_TRANS.TRANS_DATE, 'DD/MM/YYYY'), 'NULL') AS CASE_DATE

I want to get data from the view using the Between date filter, initially was working and now is no longer working. It is coming with this error

"a non-numeric character was found where a numeric was expected" *Cause: The input data to be converted using a date format model was incorrect. The input data did not contain a number where a number was required by the format model. *Action: Fix the input data or the date format model to make sure the elements match in number and type. Then retry the operation. 

     SELECT * FROM VIEW_info   
  WHERE CASE_DATE BETWEEN TO_DATE('&ENTER_START_DATE', 'DD/MM/YYYY')  
     AND TO_DATE('&ENTER_END_DATE',  'DD/MM/YYYY')

I tried this as well but is giving only extracting 5 info from thodsands of records 

 SELECT * FROM VIEW_info
 WHERE CASE_DATE BETWEEN TO_CHAR(TO_DATE('&ENTER_START_DATE', 
'DD/MM/YYYY'), 'DD/MM/YYYY')  AND TO_CHAR(TO_DATE('&ENTER_END_DATE',  
'DD/MM/YYYY'), 'DD/MM/YYYY')

Upvotes: 0

Views: 90

Answers (2)

Pavel Smirnov
Pavel Smirnov

Reputation: 4799

First you convert DATE to CHAR:

TO_CHAR(D_TRANS.TRANS_DATE, 'DD/MM/YYYY') AS CASE_DATE

Then you convert a string value to date:

TO_DATE('&ENTER_START_DATE', 'DD/MM/YYYY')

and comparing CHAR and DATE datatypes.
That's the cause of your error.

If you are working with dates, why do you convert it to CHAR in the first place?
If you still need an additional CHAR column, just add it to the view, do not replace the existing one.

Upvotes: 0

Littlefoot
Littlefoot

Reputation: 142753

That just can't work; stings are sorted differently from dates (or numbers), so '11' comes before '2'.

Your best option is to work with dates, if CASE_DATE is a date. Is it? Then you'd

select * from view_info
where case_date between to_date('&start_date', 'dd/mm/yyyy')
                    and to_date('&end_date'  , 'dd/mm/yyyy')

If it is a string (VARCHAR2 column), then you'll have to convert it to date first (presuming that format is dd/mm/yyyy), e.g.

select * from view_info
where to_date(case_date, 'dd/mm/yyyy') between to_date('&start_date', 'dd/mm/yyyy')
                                           and to_date('&end_date'  , 'dd/mm/yyyy')

As you can never know what users enter as strings, CASE_DATE might easily be '09/04/2019' or 'dd/fx/234v' or '$$75x#f9' and you won't be able to TO_DATE such values, and your query will fail.

Upvotes: 2

Related Questions