Date update problem

Kam_oracle_apex

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

mathguy

This seems trivial; what did you try?

Kam_oracle_apex

https://community.oracle.com/tech/developers/discussion/comment/16822047#Comment_16822047

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

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

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

https://community.oracle.com/tech/developers/discussion/comment/16822051#Comment_16822051

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

https://community.oracle.com/tech/developers/discussion/comment/16822052#Comment_16822052

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

Thanks a lot.

Regards

Kamran

BluShadow

https://community.oracle.com/tech/developers/discussion/comment/16822050#Comment_16822050

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

@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.