No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 9400
3 B. Vroman 4800
4 P. Wisse 4300
5 J. Schnackenberg 2900
6 J. PĂ©ran 2000
7 . Lauri 1000
8 F. Pachot 500
9 Z. Hudec 400
9 G. Lambregts 400
9 N. Havard 400
9 T. Boles 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48524
Total active users1409
Act. users last 24h3
Act. users last hour0
Registered user hits last week77
Registered user hits last month415
Go up

convert date format
Next thread: Oracle to stop spilled transaction
Prev thread: EMCC 13 configuration for new targets

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...... Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts 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...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Michel Cadot Jun 10, 2020, 17:16
Just an update, maybe someone else has the same is...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts John Baet Jun 12, 2020, 12:51

Follow up by mail Click here


Subject: convert date format
Author: John Baet, Netherlands
Date: Jun 10, 2020, 15:01, 58 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, 58 days ago
Score:   Score: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 PtsScore: 500 Pts
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, 58 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, 58 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
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, 56 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
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