CODEWITHSUNDEEP
datedatetimeoracle10gto-date
NealWalters
NealWalters

Reputation: 18187

Oracle - Get date from date time using to_date()

I getting an XML date/time from an XML file and need to load it in a datatype of just Date, so I have to truncate the time.

I would like to try something like this in order to let Oracle do the truncation: 

TO_DATE('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD')

I verify this failed by running this: 

SELECT TO_DATE('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD') FROM DUAL

It throws error: ORA-01830: data format picture ends before converting entire string.

I'm trying to minimize change to my C# program that is building the SQL statements. If I need to, I can change my C# code to generate this: 

TO_DATE('2015-11-04','YYYY-MM-DD')

Can Oracle handle the truncation or must I do it in my program?

Upvotes: 0

Views: 2091

Answers (2)

Alex Poole
Alex Poole

Reputation: 191255

You can use the to_timestamp_tz() function to convert the string from XML into a timestamp with timezone value:

SELECT TO_TIMESTAMP_TZ('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD HH24:MI:SS.FFTZH:TZM')
FROM DUAL;

TO_TIMESTAMP_TZ('2015-11-0413:45:19.387-05:00','YYYY-MM-DDHH24:MI:SS.FFTZH:TZM')
--------------------------------------------------------------------------------
04-NOV-15 13.45.19.387000000 -05:00

You can then use the trunc() function to truncate the time portion to midnight; this also converts it implicitly from a timestamp to a date:

SELECT TRUNC(TO_TIMESTAMP_TZ('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD HH24:MI:SS.FFTZH:TZM'))
FROM DUAL;

TRUNC(TO_TIMESTAMP_TZ('2015-11-0413:45:19.387-05:00','YYYY-MM-DDHH24:MI:SS.FFTZH
--------------------------------------------------------------------------------
04-NOV-15

This ignores the actual time zone - essentially assuming the values are in your system timezone (i.e. you are in the same -05:00 region).

You could also use a substring to strip the time and timezone part from the raw string before converting:

SELECT TO_DATE(SUBSTR('2015-11-04 13:45:19.387-05:00', 1, 10), 'YYYY-MM-DD')
FROM DUAL;

TO_DATE(SUBSTR('2015-11-0413:45:19.387-05:00',1,10),'YYYY-MM-DD')
-----------------------------------------------------------------
04-NOV-15

... or performing the substring in C# if you prefer, assuming that is parsing the XML document.

(These are intentionally displaying in a format that is different from the ISO standard so they don't look like the original string value from your XML; they are actual date/timestamp with time zone types, my client is just using my NLS settings.)

You also have the option of using Oracle's built-in XML handling to extract relational data from your document, but that's rather off-topic and might not be appropriate.

Upvotes: 2

Boneist
Boneist

Reputation: 23578

If you're storing your information in a DATE column in Oracle (which accepts date-and-time), then the following should help you see what you need to do:

select to_timestamp_tz('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD hh24:mi:ss.ff3tzh:tzm') tz, 
       to_date(to_char(to_timestamp_tz('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD hh24:mi:ss.ff3tzh:tzm'), 'dd/mm/yyyy hh24:mi:ss'), 'dd/mm/yyyy hh24:mi:ss') tz_char_date,
       cast(to_timestamp_tz('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD hh24:mi:ss.ff3tzh:tzm') as date) tz_cast_date
from   dual;

TZ                                       TZ_CHAR_DATE             TZ_CAST_DATE              
---------------------------------------- ------------------- ---------------------
04/11/2015 13:45:19.387000000 -05:00     04/11/2015 13:45:19 2015-11-04 13:45:19

An alternative is to use substr to shorten the string to get the portion you're interested in:

select to_date(substr('2015-11-04 13:45:19.387-05:00', 1, 19), 'yyyy-mm-dd hh24:mi:ss') substr_date
from   dual;

SUBSTR_DATE          
---------------------
2015-11-04 13:45:19

Upvotes: 2

Related Questions