| Updating a Hijri date in oracle date column [message #687911] |
Thu, 20 July 2023 04:31  |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Am trying to convert the regular date column to hijri date and update in another date colum, but end up with error as ORA-01843: not a valid month. Both the column EMPD_ISS_DT and EMPD_ISS_DT_HIJR are DATE columns.
UPDATE PM_EMP_DOCUMENT SET EMPD_ISS_DT_HIJR = TO_CHAR(EMPD_ISS_DT,'DD/MM/YYYY','NLS_CALENDAR=''Arabic Hijrah''') WHERE EMPD_CODE = '13701';
SELECT TO_CHAR(EMPD_ISS_DT,'DD/MM/YYYY','NLS_CALENDAR=''Arabic Hijrah''') FROM PM_EMP_DOCUMENT WHERE EMPD_CODE = '13701' AND EMPD_DOCU_CODE = 'Lab';
|
|
|
|
| Re: Updating a Hijri date in oracle date column [message #687912 is a reply to message #687911] |
Thu, 20 July 2023 07:22  |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
TO_CHAR is used for converting a DATE datatype to a CHARACTER datatype for the purpose of displaying it in a particular format.
If, for some reason you want to save that format in another column, then that other column should be VARCHAR2, not DATE.
Please see the demonstration below.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pm_emp_document
2 (empd_code NUMBER,
3 empd_iss_dt DATE,
4 empd_iss_dt_hijr VARCHAR2(30))
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO pm_emp_document
2 VALUES (13701, SYSDATE, NULL)
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> UPDATE PM_EMP_DOCUMENT
2 SET EMPD_ISS_DT_HIJR = TO_CHAR(EMPD_ISS_DT,
3 'DD/MM/YYYY',
4 'NLS_CALENDAR=''Arabic Hijrah''')
5 WHERE EMPD_CODE = '13701'
6 /
1 row updated.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM pm_emp_document
2 /
EMPD_CODE EMPD_ISS_D EMPD_ISS_DT_HIJR
---------- ---------- ------------------------------
13701 20/07/2023 02/01/1445
1 row selected.
|
|
|
|
| Re: Updating a Hijri date in oracle date column [message #687922 is a reply to message #687912] |
Fri, 21 July 2023 23:41 |
chat2raj.s
Messages: 161
Registered: October 2010
Location: Chennai, India
|
Senior Member |
|
|
Thank you for your example.
I can see in other tables similar Gregorian and Hijri dates are saved in oracle DATE columns.
From the vendor front end application, the conversion is handled and it works fine. When i try it from the backend only i get this error.
|
|
|
|
| Re: Updating a Hijri date in oracle date column [message #687923 is a reply to message #687922] |
Sat, 22 July 2023 00:26 |
|
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
I suppose you could wrap a TO_DATE around the TO_CHAR, as shown below.
SCOTT@orcl_12.1.0.2.0> CREATE TABLE pm_emp_document
2 (empd_code NUMBER,
3 empd_iss_dt DATE,
4 empd_iss_dt_hijr DATE)
5 /
Table created.
SCOTT@orcl_12.1.0.2.0> INSERT INTO pm_emp_document
2 VALUES (13701, SYSDATE, NULL)
3 /
1 row created.
SCOTT@orcl_12.1.0.2.0> UPDATE PM_EMP_DOCUMENT
2 SET EMPD_ISS_DT_HIJR = TO_DATE (TO_CHAR(EMPD_ISS_DT,
3 'DD/MM/YYYY',
4 'NLS_CALENDAR=''Arabic Hijrah'''),
5 'DD/MM/YYYY')
6 WHERE EMPD_CODE = '13701'
7 /
1 row updated.
SCOTT@orcl_12.1.0.2.0> SELECT * FROM pm_emp_document
2 /
EMPD_CODE EMPD_ISS_D EMPD_ISS_D
---------- ---------- ----------
13701 21/07/2023 03/01/1445
1 row selected.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
