Home » SQL & PL/SQL » SQL & PL/SQL » date conversion (oracle 11g)
date conversion [message #673979] Thu, 20 December 2018 07:43 Go to next message
guddu_12
Messages: 219
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 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 Go to previous messageGo to next message
_jum
Messages: 573
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 #673982 is a reply to message #673980] Thu, 20 December 2018 08:20 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi,

thanks it is working fine, I could not understand substr part, can you please share the logic
Re: date conversion [message #673983 is a reply to message #673982] Thu, 20 December 2018 08:26 Go to previous messageGo to next message
cookiemonster
Messages: 13894
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 #673987 is a reply to message #673982] Thu, 20 December 2018 10:13 Go to previous messageGo to next message
guddu_12
Messages: 219
Registered: April 2012
Location: UK
Senior Member
Hi

There are some records which ends with "z", do we have any date format to load them as well

2018-04-16T07:27:55Z
Re: date conversion [message #673988 is a reply to message #673987] Thu, 20 December 2018 10:17 Go to previous message
Michel Cadot
Messages: 67363
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
Previous Topic: Count function
Next Topic: Date for Sunday prior to given date
Goto Forum:
  


Current Time: Sun Sep 20 12:49:24 CDT 2020