date conversion [message #673979] |
Thu, 20 December 2018 07:43 |
|
guddu_12
Messages: 227 Registered: April 2012 Location: UK
|
Senior Member |
|
|
Dear Guru,
I am loading data from xml to oracle and I have date coming in date and time format while data type defined in oracle is date which can except those value, There is a letter "T" coming from the xml file in the data which cause loads to fail
below is the example of 1 of the records in date format from xml
"2018-03-28T22:30:21.7910503"
Can somebody advise me as how to load these values
Thanks
|
|
|
Re: date conversion [message #673980 is a reply to message #673979] |
Thu, 20 December 2018 07:57 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
You need to either substr it first to get rid of the fractional seconds, or convert to a timestamp and then a date:
SQL> WITH DATA AS (SELECT '2018-03-28T22:30:21.7910503' a FROM dual)
2 SELECT to_date(substr(a, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS'),
3 cast(to_timestamp(a, 'YYYY-MM-DD"T"HH24:MI:SS.FF') AS DATE)
4 FROM DATA;
TO_DATE(SUBSTR(A,1,19),'YYYY-MM-DD"T"HH24:MI:SS') CAST(TO_TIMESTAMP(A,'YYYY-MM-DD"T"HH24:MI:SS.FF')ASDATE)
------------------------------------------------- --------------------------------------------------------
28/03/2018 22:30:21 28/03/2018 22:30:21
SQL>
|
|
|
Re: date conversion [message #673981 is a reply to message #673979] |
Thu, 20 December 2018 07:59 |
_jum
Messages: 577 Registered: February 2008
|
Senior Member |
|
|
For example fn:current-dateTime gives such a string.
Here some different functions for getting date/datetime:
SELECT x.*
FROM xmltable('/RESULT'
PASSING XMLTYPE('<RESULT>Test</RESULT>')
COLUMNS
result VARCHAR2(100) PATH '.',
heutdt VARCHAR2(100) PATH 'current-dateTime()',
heutti VARCHAR2(100) PATH 'current-time()',
heutsd VARCHAR2(100) PATH 'xxx' DEFAULT sysdate,
heutdd DATE PATH 'xxx' DEFAULT sysdate,
heutts VARCHAR2(100) PATH 'xxx' DEFAULT to_char(systimestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF6'),
testco VARCHAR2(100) PATH 'fn:current-dateTime()+xs:dayTimeDuration("PT2H")',
testtz VARCHAR2(100) PATH 'fn:adjust-dateTime-to-timezone(fn:current-dateTime())') x;
RESULT HEUTDT HEUTTI HEUTSD HEUTTS
Test 2018-12-20T13:54:33.455283+00:00 13:54:33.455267+00:00 20.12.18 18.12.0020
[Updated on: Thu, 20 December 2018 08:02] Report message to a moderator
|
|
|
|
Re: date conversion [message #673983 is a reply to message #673982] |
Thu, 20 December 2018 08:26 |
cookiemonster
Messages: 13943 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Really?
substr is a basic function after all, and I'm not doing anything complicated with it. I'm using it to get rid of the fractional seconds, something you could easier have checked yourself:
SQL> WITH DATA AS (SELECT '2018-03-28T22:30:21.7910503' a FROM dual)
2 SELECT to_date(substr(a, 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS') AS to_date_version,
3 substr(a, 1, 19) AS substr_result,
4 cast(to_timestamp(a, 'YYYY-MM-DD"T"HH24:MI:SS.FF') AS DATE) AS to_timestamp_verison
5 FROM DATA;
TO_DATE_VERSION SUBSTR_RESULT TO_TIMESTAMP_VERISON
--------------- ------------------- --------------------
28/03/2018 22:3 2018-03-28T22:30:21 28/03/2018 22:30:21
SQL>
|
|
|
|
Re: date conversion [message #673988 is a reply to message #673987] |
Thu, 20 December 2018 10:17 |
|
Michel Cadot
Messages: 68694 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
Same a "T":
SQL> with
2 data as (
3 SELECT '2018-03-28T22:30:21.7910503' a FROM dual
4 union all
5 SELECT '2018-04-16T07:27:55Z' a FROM dual
6 )
7 select cast(to_timestamp(a, 'YYYY-MM-DD"T"HH24:MI:SS.FF"Z"') AS DATE) AS to_timestamp_verison
8 from data
9 /
TO_TIMESTAMP_VERISO
-------------------
28/03/2018 22:30:21
16/04/2018 07:27:55
|
|
|