Forum Stats

  • 3,826,571 Users
  • 2,260,666 Discussions
  • 7,897,004 Comments

Discussions

Date update problem

Kam_oracle_apex
Kam_oracle_apex Member Posts: 388 Bronze Badge

Hi Friends

I have a table where 4 fields are created

att_day, att_month, att_year, attend_date

These att_day, att_month, att_year datatype is varchar2 and attend_date is date

att_day, att_month, att_year, attend_date

01 01 2022

02 02 2022

I want above varchar fileds data to be update in attend_date as date like 01/01/2022, 02/02/2022.

Please help how can i do this.

Regards

Kam

Answers

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    This seems trivial; what did you try?

  • Kam_oracle_apex
    Kam_oracle_apex Member Posts: 388 Bronze Badge

    Actually all three fields have separated data

    att_day have 01

    att_month have 02

    att_year have 2022

    att_day att_month att_year

    01 02 2022

    02 02 2022

    Above data is actually date which i want in one field which is att_date like 02/02/2022.

    I wrote below query but not working

    UPDATE ATT_DAT_FILE

    SET ATT_DATE = to_date(to_char(ATT_DAT)||'/'||to_char(ATT_MONTH)||'/'||to_char(ATT_YEAR),'dd/mm/yyyy');

    Regards

    Kam

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    You said the first three columns are VARCHAR2 data type, so you shouldn't need to apply TO_CHAR to them. However, applying TO_CHAR to something that is already of character data type doesn't hurt anything - you just get the input string as the output.

    Other than that, what you wrote seems correct. So, what happened? Did it not work? Did you get an error?

    Note that you WOULD get an error if, for example, one of the entries in the month column was the string 'FEB' instead of '02', or if the date is invalid (like, for example, having '33' in the ATT_DAT column).

  • mathguy
    mathguy Member Posts: 10,539 Blue Diamond

    I also see a typo: you said the "day" column is named ATT_DAY but in your code you have ATT_DAT. Is that a typo in your posting here, or do you have the same in your actual code? Obviously that would make it "not work".

  • Kam_oracle_apex
    Kam_oracle_apex Member Posts: 388 Bronze Badge

    its att_day not att_dat.....typo mistake

    error is

    ERROR at line 2:

    ORA-01858: a non-numeric character was found where a numeric was expected

  • Kam_oracle_apex
    Kam_oracle_apex Member Posts: 388 Bronze Badge

    Its done. Actually it was a data problem which I found out.

    Thanks a lot.

    Regards

    Kamran

  • BluShadow
    BluShadow Member, Moderator Posts: 41,978 Red Diamond


    Actually you wouldn't get an error for FEB instead of 02, as Oracle is pretty clever at recognising obvious things like that...

    SQL> with t(att_day, att_month, att_year) as (-- attend_date
      2    select '01','01','2022' from dual union all
      3    select '02','02','2022' from dual union all
      4    select '03','FEB','2022' from dual
      5    )
      6  --
      7  select att_day, att_month, att_year
      8        ,to_date(att_day||'/'||att_month||'/'||att_year,'dd/mm/yyyy') as attend_date
      9  from   t
     10  /
    
    AT ATT ATT_ ATTEND_DATE
    -- --- ---- --------------------
    01 01  2022 01-JAN-2022 00:00:00
    02 02  2022 02-FEB-2022 00:00:00
    03 FEB 2022 03-FEB-2022 00:00:00
    

    ... as long as the NLS settings are appropriate for the language used i.e. it would complain if I used the French MAI instead of MAY with my English NLS settings.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,472 Red Diamond
    edited Jan 15, 2022 1:00PM

    @Kam_oracle_apex: Its done. Actually it was a data problem which I found out.

    And you will sooner or later run into it again. Storing day parts as separate columns is bad design. Storing them as strings and not as numbers makes it even worse. But if you must store day parts as separate columns youc, at least, need to add check constraint. Otherwise:

    create table tbl(
                     att_day   varchar2(2),
                     att_month varchar2(2),
                     att_year  varchar2(5)
                    )
    /
    insert
      into tbl values(
                      'x',
                      'y',
                      'z'
                     )
    /
    
    1 row created.
    
    SQL> insert
      into tbl values(
                      '29',
                      '2',
                      '2022'
                     )
    /
    
    1 row created.
    
    SQL> rollback
      2  /
    
    Rollback complete.
    
    SQL>
    
    

    As you can see we just inserted logically corrupt data. Check constraint would prevent that:

    alter table tbl
      add constraint tbl_chk1
        check(to_date(att_day || '/' || att_month || '/' || att_year,'dd/mm/yyyy') <= date '9999-12-31')
    /
    
    
    Table altered.
    
    
    SQL> insert
      2    into tbl values(
      3                    'x',
      4                    'y',
      5                    'z'
      6                   )
      7  /
                      'x',
                      *
    ERROR at line 3:
    ORA-01858: a non-numeric character was found where a numeric was expected
    
    SQL> insert
      2    into tbl values(
      3                    '29',
      4                    '2',
      5                    '2022'
      6                   )
      7  /
                      '29',
                      *
    ERROR at line 3:
    ORA-01839: date not valid for month specified
    
    SQL> insert
      2    into tbl values(
      3                    '28',
      4                    '2',
      5                    '2022'
      6                   )
      7  /
    
    1 row created.
    
    SQL>
    

    SY.