SQL To_Date table operations

Question

Basically I have the following query that works but doesn't give the right data:

SELECT a.* FROM 
  ( SELECT a.*, rownum rnum FROM ( 

     SELECT 

      edate.expiration_date

      FROM 
      ...

      ( SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') expiration_date 
       FROM  ... ) edate
  ) a WHERE rownum <= 20) 
 a WHERE rnum >= 1 AND 
 expiration_date < to_date('1/29/2010', 'MM/DD/YYYY HH24:MI:SS')

The reason it doesn't work is that because the rownum/rnum evaluations are done concurrently with the date checking, it only gets rownums (for example) 1, 4, 6, 9 that have dates before 1/29/2010, instead of the first 20 dates that have a date less than 1/29/2010.

So basically the area

expiration_date < to_date('1/29/2010', 'MM/DD/YYYY HH24:MI:SS')

has to be put inside the inner SELECTs, but whenever I try to do it I get an invalid month error. How can I cast the selects or the subqueries into to_dates so that it works?

Answer 1

So, leaving aside all the pagination-related stuff around it you want this query:

SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') expiration_date 
  FROM ... 
 WHERE To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS')
       < to_date('1/29/2010', 'MM/DD/YYYY')

Just running that on its own, does it fail?

Answer 2

It seems likely to me that c.value contains values which are not dates. It's just that none of them are in the first twenty rows. So the query succeeds when the TO_DATE() is applied in the outer query, because the conversion is only applied to the first twenty values. However, applying the conversion in the inner query means attempting to convert all the rows, including those that contain values which are not dates.

This is one of the pitfalls of using a database design which holds data in generic string columns instead of using the pertinent datatypes.

What to do about it? Well obviously you could change your data model so that dates are held in columns defined using the DATE datatype, but I suspect that might be more work than you're looking for. The alternative would be to use a kludge like this

create or replace function is_a_date 
    (p_str in varchar2
     , p_mask in varchar2 := 'MM/DD/YYYY HH24:MI:SS')
    return date
is
    return_value date;
begin
    begin
        return_value := to_date(p_str, p_mask);
    exception
        when others then
            return_value := null;
    end;
    return return_value;
end;
/

This takes a string and attempts to convert it to a date using the format mask. If it is a date in the appropriate format it returns the date, otherwise it returns null:

SQL> select is_a_date('01/01/2000 23:56:07') from dual
  2  /

IS_A_DATE
---------
01-JAN-00

SQL> select is_a_date('APC is not a date') from dual
  2  /

IS_A_DATE
---------


SQL>

Answer 3

I really recommend you fix the data model and store date values in a separate column. Failing that, you could try adding

where ltrim(substr(c.value,1,3),'0 ') in ('1/','2/',...'12/')

If the incoming value is "1/31/2010" rather than "01/31/2010" or " 1/31/2010" you need to change the filter accordingly. Look at REGEX matching.

But unless you fix that model, you will waste a lot more time on this

Answer 4

Based on the information in your question, the 1st three answers and subsequent comments, it actually sounds like it has nothing to do with the 1st 20 values, that is a red herring. It sounds like it might have to do with the hidden logic in the inner most select. I believe that something in there is removing records from Table C so that the TO_DATE on C.Value is not occurring on the fields that are not valid dates, but when you add the To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') < to_date('1/29/2010', 'MM/DD/YYYY') to your inner most SQL it is occurring on all the fields in Table C. You should be able to avoid this by simply switching your outer and first embedded WHERE clauses

SELECT a.* FROM 
  ( SELECT a.*, rownum rnum FROM 

      ( SELECT 

               edate.expiration_date

        FROM 
        ...

          ( SELECT To_Date(c.Value, 'MM/DD/YYYY HH24:MI:SS') expiration_date 
            FROM  ... ) edate
      ) a 
    WHERE expiration_date < to_date('1/29/2010', 'MM/DD/YYYY HH24:MI:SS')
  )  a 
WHERE rnum >= 1 AND 
      rownum <= 20

If this is not possible due to other logic that can't be changed, you should be able to add a WHERE clause inside the inner most "a" for expiration_date < TO_DATE('1/29/2010', 'MM/DD/YYYY')

I, personally, would try and fix the data if it is invalid, so you or others don't run into this again in the future, but you can also use APC's function when accessing this field in the future.