No question at this time
The DBA-Village forum
as RSS feed
Site Statistics| Ever registered users | 48536 | | Total active users | 1398 | | Act. users last 24h | 2 | | Act. users last hour | 0 | | Registered user hits last week | 128 | | Registered user hits last month | 434 |
|
Go up
convert date format
| Message |
Score |
Author |
Date |
   Hi all,
I need to convert C09 format IN TABLE f...... |
|
John Baet |
Jun 10, 2020, 15:01 |
|
>>> <i> I need to convert C09 format IN TABLE fr...... |
  |
Michel Cadot |
Jun 10, 2020, 16:32 |
| Hi Michel,
First of all thank you for you reply...... |
|
John Baet |
Jun 10, 2020, 17:10 |
|
Most likely the error comes from the data in C09...... |
|
Michel Cadot |
Jun 10, 2020, 17:16 |
| Just an update, maybe someone else has the same is...... |
|
John Baet |
Jun 12, 2020, 12:51 |
| Subject: |
convert date format |
| Author: |
John Baet, Netherlands |
| Date: |
Jun 10, 2020, 15:01, 107 days ago |
| Os info: |
Linux |
| Oracle info: |
12.2 |
| Error info: |
<b> ORA-01821: date format not recognized </b> |
| Message: |
Hi all,
I need to convert C09 format IN TABLE from "17:30:52:500 31 MAR 2020" to "20200331173052500"
I tried below but I am getting error: ORA-01821: date format not recognized
Is there any other way?
desc test
Name Null? Type
----------------------------------------- -------- ----------------------------
RECID NOT NULL VARCHAR2(255)
XMLRECORD SYS.XMLTYPE
SELECT ID, RECID, C01, C02, C03, C04, C05, C06, C07, C08, DATUM_PROC, C10
FROM (
SELECT REGEXP_SUBSTR(RECID,'(\d+)') AS ID, RECID, C01, C02, C03, C04, C05, C06, C07, C08, TO_CHAR(TO_DATE(C09,'HH24:MI:SS:FFF DD MON YYYY'),'YYYYMMDDHH24MISSFFF') AS DATUM_PROC, C10
FROM TEST,
XMLTABLE('/row'
PASSING TEST.XMLRECORD
COLUMNS
C01 VARCHAR(40) Path 'c1',
C02 VARCHAR(40) Path 'c2',
C03 VARCHAR(40) Path 'c3',
C04 VARCHAR(40) Path 'c4',
C05 VARCHAR(40) Path 'c5',
C06 VARCHAR(40) Path 'c6',
C07 VARCHAR(40) Path 'c7',
C08 VARCHAR(40) Path 'c8',
C09 VARCHAR(26) Path 'c9',
C10 VARCHAR(40) Path 'c10'
))
WHERE REGEXP_SUBSTR(RECID,'(^\D+)') = 'TX'
ORDER BY DATUM_PROC DESC;
Thank you for your reply. |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: convert date format |
| Author: |
Michel Cadot, France |
| Date: |
Jun 10, 2020, 16:32, 107 days ago |
| Score: |
|
| Message: |
>>> I need to convert C09 format IN TABLE from "17:30:52:500 31 MAR 2020" to "20200331173052500"
SQL> select to_char(
2 to_timestamp('17:30:52:500 31 MAR 2020',
3 'HH24:MI:SS:FF3 DD MON YYYY',
4 'NLS_DATE_LANGUAGE=ENGLISH'),
5 'YYYYMMDDHH24MISSFF3') dt
6 from dual
7 /
DT
-----------------
20200331173052500
Regards
Michel
|
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: convert date format |
| Author: |
John Baet, Netherlands |
| Date: |
Jun 10, 2020, 17:10, 107 days ago |
| Message: |
Hi Michel,
First of all thank you for you reply but I am getting below error:
ORA-01858: a non-numeric character was found where a numeric was expected
Below is what I did.
SELECT ID, RECID, C01, C02, C03, C04, C05, C06, C07, C08, DATUM_PROC, C10
FROM (
SELECT REGEXP_SUBSTR(RECID,'(\d+)') AS ID, RECID, C01, C02, C03, C04, C05, C06, C07, C08, TO_CHAR(to_timestamp(C09,'HH24:MI:SS:FF3 DD MON YYYY','NLS_DATE_LANGUAGE=ENGLISH'),'YYYYMMDDHH24MISSFF3') AS DATUM_PROC, C10
FROM TEST,
XMLTABLE('/row'
PASSING TEST.XMLRECORD
COLUMNS
C01 VARCHAR(40) Path 'c1',
C02 VARCHAR(40) Path 'c2',
C03 VARCHAR(40) Path 'c3',
C04 VARCHAR(40) Path 'c4',
C05 VARCHAR(40) Path 'c5',
C06 VARCHAR(40) Path 'c6',
C07 VARCHAR(40) Path 'c7',
C08 VARCHAR(40) Path 'c8',
C09 VARCHAR(26) Path 'c9',
C10 VARCHAR(40) Path 'c10'
))
WHERE REGEXP_SUBSTR(RECID,'(^\D+)') = 'TX'
ORDER BY DATUM_PROC DESC;
Thank you again. |
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: convert date format |
| Author: |
Michel Cadot, France |
| Date: |
Jun 10, 2020, 17:16, 107 days ago |
| Score: |
|
| Message: |
Most likely the error comes from the data in C09 but I have not your TEST table and data, so can't say more.
Regards
Michel
|
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
| Subject: |
Re: convert date format |
| Author: |
John Baet, Netherlands |
| Date: |
Jun 12, 2020, 12:51, 105 days ago |
| Score: |
|
| Message: |
Just an update, maybe someone else has the same issue.
I solved it by changing the script to below one. Business is just need those rows which inserted correctly.
select REGEXP_SUBSTR(RECID,'(\d+)') AS ID,
RECID,
extract(xmlrecord,'/row/c1/text()').getStringVal() C01,
extract(xmlrecord,'/row/c2/text()').getStringVal() C02,
extract(xmlrecord,'/row/c3/text()').getStringVal() C03,
extract(xmlrecord,'/row/c4/text()').getStringVal() C04,
extract(xmlrecord,'/row/c5/text()').getStringVal() C05,
extract(xmlrecord,'/row/c6/text()').getStringVal() C06,
extract(xmlrecord,'/row/c7/text()').getStringVal() C07,
extract(xmlrecord,'/row/c8/text()').getStringVal() C08,
TO_CHAR(to_timestamp(extract(xmlrecord,'/row/c9/text()').getStringVal(),'HH24:MI:SS:FF3 DD MON YYYY','NLS_DATE_LANGUAGE=ENGLISH'),'YYYYMMDDHH24MISSFF3') C09,
extract(xmlrecord,'/row/c10/text()').getStringVal() C10
FROM "MBTE_OWN"."V_AAB_F_OFS_REQT_DTLS_AMF"
where REGEXP_SUBSTR(RECID,'(^\D+)') = 'TX'
AND length(extract(xmlrecord,'/row/c9/text()').getStringVal()) =24 ;
|
| Your rating?: |
This reply is Good Excellent |
| Goto: |
Reply - Top of page |
If you think this item violates copyrights, please click here
|