This week's book / license giveaways are in the JDBC and Relational Databases and Java in General forums.
We're giving away four copies each of PostGIS in Action and Java Advanced Topics Training and have the authors on-line!
See this thread and this one for details.
The moose likes Oracle/OAS and the fly likes using to_timestamp to convert calendar milliseconds? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "using to_timestamp to convert calendar milliseconds?" Watch "using to_timestamp to convert calendar milliseconds?" New topic

using to_timestamp to convert calendar milliseconds?

Jane Foster

Joined: Jun 16, 2009
Posts: 13
I am trying to extract a date from some xml and insert it into a column on an Oracle table that is of type Date. The date was created using cal.setTimeInMillis and looks like this after being created:

I am trying to extract it like this:
(select to_timestamp(extractValue(xml,'/test/testDate/time/text()'))from dual),

and I get the following error:
ORA-01843: not a valid month

I know that I am getting this error because I haven't supplied a format mask. I am not sure how to do this, can anyone help me figure this out? I am not opposed to changing the way I am creating the date, I am just not sure how to go about this. Am fairly new to this. Thanks in advance -
John Bengler
Ranch Hand

Joined: Feb 12, 2009
Posts: 133
Hi Jane,

I'm afraid with only using to_timestamp it will not be be possible to perform this conversion, because Oracle doesn't supply a direct Date format for this:

DateTime Format Models

However with some extra calculation it is possible to do this. If you know that a numeric value of 1 represents a day in an Oracle date (or timestamp) field you simply can get the Date/Timestamp for your offset (e.g. for 1.1.1970: select to_date('01-JAN-1970','DD-MON-YYYY') from dual and add your milliseconds:

for timestamps you can use the function NUMTODSINTERVAL():

I agree. Here's the link:
subject: using to_timestamp to convert calendar milliseconds?