#1
  1. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7

    Masking Birthday with to_char(to_date(


    I need to make everybody's birthday default to dd/mm/'1900' so that they are masked.

    I can't seem to find the right combination.

    Code:
    UPDATE table a
    SET a.BIRTHDATE = (
     SELECT to_char(to_date(b.BIRTHDATE,'dd/mm/yyyy'),'dd/mm' || '/1900')
      FROM table b
    or

    Code:
    UPDATE table a
    SET a.BIRTHDATE = (
    SELECT nvl(to_date(to_char(b.BIRTHDATE
    ,'MM/DD') || '/1900')
    , NULL)
    FROM table b
    I get error
    ORA-01840: input value not long enough for date format



    Any help is appreciated.
  2. #2
  3. Contributing User
    Devshed Novice (500 - 999 posts)

    Join Date
    Sep 2006
    Posts
    859
    Rep Power
    388
    Try this:
    Code:
    UPDATE tablea a
       SET a.birthdate =
              (SELECT TO_DATE ( TO_CHAR ( b.birthdate, 'dd/mm') || '/1900', 'dd/mm/yyyy')
                 FROM tableb b)
  4. #3
  5. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2008
    Location
    Texas
    Posts
    354
    Rep Power
    7
    Originally Posted by LKBrwn_DBA
    Try this:
    Code:
    UPDATE tablea a
       SET a.birthdate =
              (SELECT TO_DATE ( TO_CHAR ( b.birthdate, 'dd/mm') || '/1900', 'dd/mm/yyyy')
                 FROM tableb b)
    hmmmm

    ORA-01839: date not valid for month specified


    Here is an example

    10/08/1982 00:00:00
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Regular (2000 - 2499 posts)

    Join Date
    Jan 2006
    Location
    Carlsbad, CA
    Posts
    2,057
    Rep Power
    383
    I do not know if it helps but (no database involved):
    This works for me.
    Code:
    begin
      -- Test statements here
      dbms_output.put_line('start: ');
      dbms_output.put_line(TO_DATE( TO_CHAR ( to_date('10-AUG-1951'), 'dd/mm') || '/1921', 'dd/mm/yyyy'));
      dbms_output.put_line('date: ');
    end;
    Output was

    start:
    10-AUG-21
    date:

    Are you sure all your source dates are valid?

IMN logo majestic logo threadwatch logo seochat tools logo