39

I tried to convert a time-stamp ("1985-02-07T00:00:00.000Z") to a date and I failed to succeed in my several different attempts.

Below is the query I have tried:

 select to_date('1985-02-07T00:00:00.000Z', 'YYYY-MM-DDTHH24:MI:SS.fffZ')
 from dual;

Your suggestions are greatly appreciated.

Abilash
  • 1,143
  • 3
  • 14
  • 20
  • 3
    +1 for trying stuff. You can make the question better by including what the results were and how they were wrong or what generally didn't work. (Was there an error? Was the date a few hours off?) –  Dec 28 '11 at 09:57
  • 2
    According to [to_date](http://www.techonthenet.com/oracle/functions/to_date.php) I believe that at least "fff" is wrong and should be "FF3". But I don't use/have Oracle :) –  Dec 28 '11 at 10:01

5 Answers5

58

to_date converts the input to a DATE type which does not support fractional seconds. To use fractional seconds you need to use a TIMESTAMP type which is created when using to_timestamp

pst's comment about the ff3 modifier is also correct.

"Constant" values in the format mask need to be enclosed in double quote

So the final statement is:

select to_timestamp('1985-02-07T00:00:00.000Z', 'YYYY-MM-DD"T"HH24:MI:SS.ff3"Z"')
from dual;
11
SQL> select cast(to_timestamp('1985-02-07T00:00:00.000Z', 'yyyy-mm-dd"T"hh24:mi:ss.ff3"Z"') as date)
  2    from dual
  3  /

CAST(TO_TIMESTAMP('
-------------------
07-02-1985 00:00:00

1 row selected.
Stefan van den Akker
  • 6,661
  • 7
  • 48
  • 63
Rob van Wijk
  • 17,555
  • 5
  • 39
  • 55
5
SELECT to_timestamp_tz('2012-08-08T09:06:14.000-07:00','YYYY-MM-DD"T"HH24:MI:SS.FF3TZR')
FROM dual;

External table DDL,
extract_date char(29) DATE_FORMAT timestamp WITH TIMEZONE mask 'YYYY-MM-DD"T"HH24:MI:SS.FF3TZR'
Jake1164
  • 12,291
  • 6
  • 47
  • 64
Charlie 木匠
  • 31
  • 1
  • 3
5

Some rules to follow:

  1. Literals must be double-quoted: MM expects a month number, "MM" expects a double-M.
  2. The format for fractional seconds is FF, not F or FFF. You specify the number of digits with a trailing integer, e.g. FF3.
  3. But dates cannot hold fractional seconds anyway so you cannot use FF3 in this context.

This works:

SELECT TO_DATE('1985-02-07T00:00:00', 'YYYY-MM-DD"T"HH24:MI:SS')
FROM dual;

I don't know if there's a way to ignore fractional seconds in TO_DATE() so I've used string manipulation functions to strip them out:

SELECT TO_DATE(SUBSTR('1985-02-07T00:00:00.000Z', 1, 19), 'YYYY-MM-DD"T"HH24:MI:SS')
FROM dual;
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
-1

if u want to get the string in datetime format then try this....

select to_char(TO_DATE('2012-06-26T00:00:00.809Z', 'YYYY-MM-DD"T"HH24:MI:SS".""ZZZZ"'),'yyyy-MM-dd hh:mm:ss PM') as EVENT_DATE from dual

EVENT_DATE
-----------------------
2012-06-26 12:06:00 AM

only for date simply use...

select TO_DATE('2012-01-06T00:00:00.809Z', 'YYYY-MM-DD"T"HH24:MI:SS".""ZZZZ"') from dual
Vincent G
  • 361
  • 1
  • 4
  • 22
user4524067
  • 1