Oracle - Get date from date time using to_date()

Question

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?

What timezone are all your dates being stored as? Are they all GMT-5? Also is it a business requirement that you don't need to store the times being sent in by the xml? Because Oracle's DATE and TIMESTAMP datatypes will both quite happily accept times (DATE to the nearest second and is timezone unaware, TIMESTAMP can store sub seconds) — Boneist, Nov 10 '15 at 15:44
According to https://docs.oracle.com/cd/E17952_01/refman-5.1-en/datetime.html the DATE type is used for values with a date part but no time part. Right now, GMT-5 is good, I can refine later. — NealWalters, Nov 10 '15 at 15:50
I'll discuss with business users if they want to store time, but column in database is of type Date. — NealWalters, Nov 10 '15 at 15:51
That link doesn't look to be talking about the Oracle database. In Oracle, the DATE datatype can store times, eg. '2015-11-10 16:00:02'. It doesn't understand timezones though, so you have to pick a timezone to stick to; usually the same timezone as the server (check what `select sysdate from dual;` returns. If that's at the GMT -5, then that's probably best to stick to. — Boneist, Nov 10 '15 at 16:01
Sorry, I guess that was Oracle/MySQL. Anyhow, my question is not yet about timezone. It's how to avoid the ORA-01830: data format picture ends before converting entire string. I also tried this: select TO_DATE('2015-11-04 13:45:19.387-05:00','YYYY-MM-DD HH24:MI:SS') from dual. Do I need to include the TimeZone in the format string? — NealWalters, Nov 10 '15 at 16:06
I've given you some methods on how to achieve your goal in my answer. Hopefully that'll help you! — Boneist, Nov 10 '15 at 16:15

Boneist · Accepted Answer · 2015-11-10T16:19:09.713

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

Alex Poole · Answer 2 · 2015-11-10T16:40:26.137

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.

(you could do `alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';` prior to running your statements to get them in the right format for displaying here, although I'm sure you already knew that!) — Boneist, Nov 10 '15 at 16:21
@boneist - yes, I usually do, but wanted a format that didn't look like the raw data in this case so I left my DB's (silly) defaults *8-) — Alex Poole, Nov 10 '15 at 16:22

Oracle - Get date from date time using to_date()

2 Answers2