Needing help converting a date to useable format (2012-03-08T18:08:00Z)

[Matt Kohanek]

I am trying to invoke a secure web service, and I have to fill in an element>:
<wsu:Created></wsu:Created>

The only date format I can get to work is 2012-03-08T18:08:00Z
And that has to be UTC time. So I need to find out how to get the current UTC time into that format from SYSDATE.

So far, I can get SYSDATE in UTC:

select sys_extract_utc(systimestamp) INTO v_curr_date from dual;

That gives me the following:
3/8/2012 6:13:00.446294 PM

However, if I use that I am told the date in unparseable..
: java.text.ParseException: Unparseable date: "08-MAR-12 06.13.38.129924 PM"

Can anyone help me get past this last step of converting that date to the format I need it in?

[Paul Clapham]

I don't understand why you need to use a database for this. Why can't you just use a Java Date object and format it appropriately? Did I miss something in your problem description?

[Matt Kohanek]

I did not mention I am invoking this service from PL SQL. I am not able to change the web service itself at the moment.
I am creating a wrapper API in our PL SQL code that will invoke this service - in it I am creating the soap request (including this security header)

So is the format I am asking for not a standard one (meaning no proc/func that will do the conversion)? And if not, I am sure there is a way to finagle it into the format I need - is this what I should be looking into?

[Martin Vashko]

I'd probably be inclined to do the conversion in Java, but of course it is doable in Oracle using the to_char() function:

select to_char(cast(systimestamp at time zone 'UTC' as date), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;

See also Oracle's Datetime Format Models.

Edit: Oracle supports Java stored procedures, if you need to do a lot of processing in the database and are not that much familiar with PL/SQL, you might try them out, settign the thing up will take some learning too.

[Matt Kohanek]

Martin Vajsar wrote:I'd probably be inclined to do the conversion in Java, but of course it is doable in Oracle using the to_char() function:

select to_char(cast(systimestamp at time zone 'UTC' as date), 'YYYY-MM-DD"T"HH24:MI:SS"Z"') from dual;

See also Oracle's Datetime Format Models.

Edit: Oracle supports Java stored procedures, if you need to do a lot of processing in the database and are not that much familiar with PL/SQL, you might try them out, settign the thing up will take some learning too.

Martin - that is similar to what I had in mind when I asked if I would need to finagle the output - I had just not yet figured out the exact syntax to do it.
Thanks very much for the code, I will go with that for now.

I am familiar with Java stored procedures in pl sql - I have written a couple actually. I am much more familiar with PL SQL than java actually.

But you mention doing this conversion in the java. That interests me because I think I looked at this incorrectly in some sense. My understanding was that I can create the soap request in the pl sql, and the date for the security header MUST be in the format I give. So you are saying I can alter the java code so that it accepts the date in some other format? - I can make the java accept something like
<wsu:Created>3/8/2012 6:13:00.446294 PM </wsu:Created>
?

I know this is possible for parameter values I send in to the service <arg1>anydate</arg1> - etc
But since <wsu:Created> is part of the security header, I assumed this date format was all or none.

Is this assumption incorrect then?

Thanks

[Martin Vashko]

Matt Kohanek wrote:But you mention doing this conversion in the java. (...)

That was a misunderstanding on my part. I, like Paul, assumed you're creating the request in Java, and just take the date from the database as an "authorized source".

I've no idea whether the format in the webservice header can be overriden. But even if it was, obtaining the correct format the way you did would still seem better to me.