No question at this time
DBA Top 10
1 M. Cadot 10400
2 A. Kavsek 7600
3 B. Vroman 4800
4 P. Wisse 4000
5 J. Schnackenberg 3200
6 J. PĂ©ran 2200
7 T. Boles 600
8 Z. Hudec 400
8 G. Lambregts 400
8 N. Havard 400
8 F. Pachot 400
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48536
Total active users1398
Act. users last 24h2
Act. users last hour0
Registered user hits last week128
Registered user hits last month434
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, 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:   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, 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:   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, 105 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