This content has been marked as final. Show 4 replies
Don't know if there is an easier way:
SQL> SELECT TO_CHAR (SYSTIMESTAMP, 'dd.mm.rrrr hh24:mi:ss.ff') a, TO_CHAR (CAST (SYSTIMESTAMP AS TIMESTAMP ( 3 )), 'dd.mm.rrrr hh24:mi:ss.ff' ) b FROM DUAL A B ----------------------------- ----------------------------- 07.02.2007 18:30:46.523728 07.02.2007 18:30:46.524
Thanks for the reply, but this is in an external table creation. eg:
CREATE TABLE TEST_TABLE(
ORGANIZATION EXTERNAL (
default directory EXT_DIRECTORY
access parameters (
records delimited by newline
FIELDS TERMINATED BY 0X'09'
missing field values are null
, DATE_1 char date_format date mask "MM/DD/YY HH24:MI:SS"
, DATE_2 char date_format date mask "MM/DD/YY HH24:MI:SS.ff"
reject limit unlimited;
The above external table compiles, but when you SELECT from it you get an invalid date mask error for the DATE_2 column.
Using no mask or the same mask as for DATE_1 I simply get no rows returned and inside the .log file I see errors for the date conversion since it does not match the default date mask.
The code you posted is for a standard SELECT which does not seem to play well inside of an external table syntax. Or if it does, I'm missing how to incorporate it correctly.
Try replacing the line
, DATE_2 char date_format date mask "MM/DD/YY HH24:MI:SS.ff"<p>
, DATE_2 char date_format TIMESTAMP mask "MM/DD/YY HH24:MI:SS.ff3"
There were two problems. One was your use of TIMESTAMP, the other was the fact that the year was actually YYYY. I had missed that.
Thank you very much, that saved me quite a bit of hair pulling.