TO_DATE and then use in the WHERE with a BETWEEN

Question

Can I take this SELECT below, substr out the 24 hour fallacy and replace it with 00 hour and then run the WHERE TRUNC(TO_DATE(FIX_DATETIME, 'MMDDYYHH24MISS')) BETWEEN TRUNC(SYSDATE-90) AND TRUNC(SYSDATE)? Can it be done in one pass? If so,whether on pass or more, how? You help is very much appreciated and your skill levels are respected.

SELECT MOPACTIVITY.MOPID,
    (CASE 
        WHEN SUBSTR(MOPACTIVITY.MOPID, 7, 2) = '24' THEN SUBSTR(MOPACTIVITY.MOPID, 1, 2)||SUBSTR(MOPACTIVITY.MOPID, 3, 2)||SUBSTR(MOPACTIVITY.MOPID, 5, 2)||'00'||SUBSTR(MOPACTIVITY.MOPID, 9, 2)||SUBSTR(MOPACTIVITY.MOPID, 11, 2)
        ELSE MOPACTIVITY.MOPID
    END ) FIX_DATETIME,
sysdate "SYSDATE"
FROM MOPUSER.MOPACTIVITY

Why are you storing dates in a varchar column? That is a sure way of getting into trouble. — a_horse_with_no_name
Big company database. I have requested the change but it isn't gonna happen. That I why I am trying to work around it. Any thoughts? — Copra Loom
The only sensible solution is to change the column to a proper DATE column. Everything else is just a hack. — a_horse_with_no_name
I hear and agree with you but the change isn't going to happen. The database isn't going to change, so I have to hack it. I believe I have presented the issue to the higher ups but I am shouting at the wind. :( No ideas how to hack around it? — Copra Loom

Justin Cave Justin Cave · Accepted Answer · 2013-06-10T18:57:22

Leaving aside for a moment the incongruity of a column named MOPID which someone would naturally assume to be some sort of numeric ID is actually a VARCHAR2 that happens to represent a DATE and the fact that you should never store a DATE as a VARCHAR2 because you will inevitably end up with invalid data in that column that will cause queries to start throwing errors seemingly at random when query plans change...

WHERE to_date(MOPACTIVITY.MOPID, 'MMDDYYHH24MISS') BETWEEN (sysdate-90) and sysdate

would do what you seem to want.

To identify the rows that have invalid data (almost certain when you use the wrong data type)

CREATE OR REPLACE FUNCTION is_valid_date( p_str IN VARCHAR2,
                                          p_mask IN VARCHAR2 )
  RETURN VARCHAR2
IS
  l_dt  DATE;
BEGIN
  l_dt := to_date( p_str, p_mask );
  RETURN 'Y';
EXCEPTION
  WHEN OTHERS THEN
    RETURN 'N';
END;

and then

SELECT *
  FROM MOPACTIVITY
 WHERE is_valid_date( MOPID, 'MMDDYYHH24MISS' ) = 'N'

You can try to find invalid data using regular expressions or by trying to SUBSTR and INSTR various components. That approach, however, can only ever be approximate unless you want to try to implement every possible calendar rule (i.e. 29-31 is a valid day in some months and some years but not others). It's generally easier to simply let to_date try the conversion and to rely on Oracle to implement all those rules.

TO_DATE and then use in the WHERE with a BETWEEN

2 Answers