Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

using to_timestamp to convert calendar milliseconds?

 
Jane Foster
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:
<theDate>
<time>1261458000000</time>
<timezone>America/Indiana/Petersburg</timezone>
</theDate>

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
Posts: 133
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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():



John
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic