Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2012
    Posts
    36

    Unanswered: Replcing Invalid dates with null

    Hi friends,
    Basically my Oracle query is this
    SELECT REFERENCE,MAIN_PROD,ACCOUNT_TYPE,ISS_ADV_DATE,MATU RITY_DT,COUNTRY_CODE,EXPOSURE_ACCOUNT;

    So going further i want to get the ISS_ADV_DATE,MATURITY_DT in Timestamp format,which is Originally stored as Varchar in DB.
    I do th e following changes
    SELECT REFERENCE,MAIN_PROD,ACCOUNT_TYPE,to_date(ISS_ADV_D ATE,'YYYY-MM-DD HH:MII',MATURITY_DT,'YYYY-MM-DD HH:MII',COUNTRY_CODE,EXPOSURE_ACCOUNT;

    It works fine only for the column which is having correct date.If invalid date comes it wont work,
    Ex:In my Column ISS_ADV_DT I Have values like '00000000' and '19999999' which is not a valid date,
    I want to replace this values as NULL,
    Can someone advise me how to replace to null if a Invalid date comes in my field.??

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    DECODE() function
    DECODE
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2012
    Posts
    36

    Date fields

    First thaks a lot,
    nd can u please tell me how can i use a date field in there..??

    My input is a Varchar but im checking it against a date column.

    Please clarify

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    20:48:10 SQL> @foobar
    20:48:14 SQL> DROP   table foobar;
    
    Table dropped.
    
    20:48:14 SQL> create table foobar (ISS_ADV_DT VARCHAR2(32));
    
    Table created.
    
    20:48:14 SQL> INSERT INTO FOOBAR VALUES('00000000');
    
    1 row created.
    
    20:48:14 SQL> INSERT INTO FOOBAR VALUES('19999999');
    
    1 row created.
    
    20:48:14 SQL> INSERT INTO FOOBAR VALUES('2012-12-31');
    
    1 row created.
    
    20:48:14 SQL> select decode(iss_adv_dt,'00000000',NULL,'19999999',NULL,ISS_ADV_DT,ISS_ADV_DT) from foobar;
    
    DECODE(ISS_ADV_DT,'00000000',NUL
    --------------------------------
    
    
    2012-12-31
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Feb 2012
    Posts
    36

    Valid date

    Thanks a lot for ur reply friend.

    I wud kindly appreciate for your reply,

    But friend,
    I cant predict what could be my Fields Invalid date values,
    Just to be more clear in my explanation i said it could be '00000000','19999999'.

    But sadly it could be any other values.

    But i have to consider only if it is a valid date.

    For example: '20120424','19990312' etc then Valid date Else If Invalid(
    Example:'20125012','20051345' etc then it has to be null.


    Thanks a ton again for ur reply friend.
    I hope this time im more clear with my issue

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    IF
    THEN
    is only valid within PL/SQL; not raw SQL

    You need to use the correct tool for the task at hand.

    When your only tool is a hammer, all problems are viewed as nails.
    While a hammer is a GREAT tool, it is suboptimal to divide 1 board into 2 pieces
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Feb 2012
    Posts
    36
    Thanks a lot for ur Information friend.

    Hope it would help me much.

    Have a great day :-)

  8. #8
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >But sadly it could be any other values.
    consider fixing the problem at the source; instead of after the horse has escaped from the barn.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  9. #9
    Join Date
    Feb 2012
    Posts
    36
    ha ha ha:-)

    I would consider your advise :-)

  10. #10
    Join Date
    Oct 2004
    Posts
    60
    You could create a function to validate the data and use it in a case statement.

  11. #11
    Join Date
    Jun 2004
    Location
    Liverpool, NY USA
    Posts
    2,509
    I would make the following function

    Code:
    CREATE OR REPLACE FUNCTION get_date(str in varchar2) RETURN date IS
    test date;
    BEGIN
       test_date := to_date(str,'YYYYMMDD');
       RETURN TEST_DATE;
       EXCEPTION
         WHEN OTHERS THEN
         RETURN NULL;
    END get_date;
    /
    SELECT GET_DATE(ISS_ADV_DT) FROM MY_TABLE;

    That being said, never store date or time in anything but a date column.
    Bill
    You do not need a parachute to skydive. You only need a parachute to skydive twice.

  12. #12
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253

    Post

    Regular expressions will solve your problem in this case.
    Code:
    dayneo@SANDBOX> create table test(id number, dtval varchar2(20))
      2  /
    
    Table created.
    
    dayneo@SANDBOX> 
    dayneo@SANDBOX> begin
      2    insert into test values(1, '01/01/2011 14:35:04'); -- valid date
      3    insert into test values(2, '23/06/2012 00:14:04'); -- valid date
      4    insert into test values(3, '11342759127'); -- invalid date value
      5    insert into test values(4, 'alsdfj38724'); -- invalid date value
      6  end;
      7  /
    
    PL/SQL procedure successfully completed.
    
    dayneo@SANDBOX> 
    dayneo@SANDBOX> select * from test
      2  /
    
            ID DTVAL
    ---------- --------------------
             1 01/01/2011 14:35:04
             2 23/06/2012 00:14:04
             3 11342759127
             4 alsdfj38724
    
    dayneo@SANDBOX> 
    dayneo@SANDBOX> update test
      2  	set dtval = null
      3   where not regexp_like(dtval, '[0-9]{2,2}\/[0-9]{2,2}\/[0-9]{4,4} [0-9]{2,2}:[0-9]{2,2}:[0-9]{2,2}')
      4  /
    
    2 rows updated.
    
    dayneo@SANDBOX> 
    dayneo@SANDBOX> select * from test
      2  /
    
            ID DTVAL
    ---------- --------------------
             1 01/01/2011 14:35:04
             2 23/06/2012 00:14:04
             3
             4
    
    dayneo@SANDBOX> 
    dayneo@SANDBOX> drop table test
      2  /
    
    Table dropped.
    
    dayneo@SANDBOX>
    My example makes use of regular expressions to test which values "look like" a date. This could still fail due to leap years, or values that look like dates but have specified too many days in month for example.

  13. #13
    Join Date
    Feb 2012
    Posts
    36
    Hi Friend,

    Thank you all for ur valuable suggestions.
    I wud implement and give u the updates.

    Thanks Again :-)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •