wood burning stoves 2.0*
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

Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
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: http://aspose.com/file-tools
subject: using to_timestamp to convert calendar milliseconds?
Similar Threads
timezone information getting lost in java.sql.date
Unable to Convert in Timestamp
TimeZone, calendar
count by current timestamp
<bean:message> and jquery