Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 439 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Date update problem

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
-
This seems trivial; what did you try?
-
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
-
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).
-
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".
-
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
-
Its done. Actually it was a data problem which I found out.
Thanks a lot.
Regards
Kamran
-
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.
-
@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.