Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Entering date from different time zones in apex

SACH!N-OracleMar 9 2018 — edited Mar 12 2018

Hi,

I have to develop an application that should support multiple time zones.

In short, a user in any timezone should be able to create a record with a date in the parent timezone (let the parent timezone be UTC).

Also the users should be able to view the records in their time-zones.

What is the best way for achieving this?

Please help/guide.

Please let me know if more information is required.

Thanks,

Sachin
This post has been answered by fac586 on Mar 12 2018
Jump to Answer

Comments

Galbarad
just convert date to char
with to_char(sysdate, 'YOUR_DATE_FORMAT') 
select xmlelement("Date", to_char(sysdate, 'YYYY-MM-DD')).getClobVal() from dual;
good luck

think you need change your cursor 
OPEN l_refcursor FOR select to_char(sysdate, 'YYYY-MM-DD') "Date" from dual;
Edited by: Galbarad on Jul 13, 2012 7:21 AM
Gert Poel
I know this is a solution, like I stated in my question: "Of course I can use to_char and specify the correct date format or do an alter session(alter session set nls_date_format='RRRR-MM-DD'; )"

But it seems that XMLElement uses the XML standard and dbms_xmlgen.getXMLType doesn't.
Maybe I should just pass this to Oracle Support...
Hoek
I believe that the desired default date format only applies for two functions:
"27.1 XML Date Formatting

Dates and timestamps in generated XML are now in the formats specified by XML Schema. See Oracle XML DB Developer's Guide, Chapter "Generating XML Data from the Database", *section "XMLELEMENT and XMLATTRIBUTES SQL Functions"*, subsection "Formatting of XML Dates and Timestamps"."
http://docs.oracle.com/cd/B19306_01/readmes.102/b14233/toc.htm#sthref85

Your quote from the docs ( http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1506 ) applies to the SQL/XML standard functions, and apparently not to DBMS_XMLGEN (one of the Oracle Database-provided functions).

"It explains the SQL/XML standard functions and Oracle Database-provided functions and packages for generating XML data from relational content."
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#ADXDB1600

"If you otherwise need to produce a non-standard XML date or timestamp, use SQL function to_char"
http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb13gen.htm#sthref1506
1 person found this helpful
MichaelS
Answer
Or is there another way to use the default XML standard?
you can revert to the java based dbms_xmlquery instead:
SQL> alter session set nls_date_format='rrrrhh24'  ---just to show the result is independent from nls_format
/
Session altered.

SQL> declare
  l_xmltype   xmltype;
  l_ctx       dbms_xmlquery.ctxhandle;
begin
  l_ctx := dbms_xmlquery.newcontext ('select sysdate "Date" from dual');
  dbms_xmlquery.setrowtag (l_ctx, null);
  dbms_xmlquery.setrowsettag (l_ctx, null);
  l_xmltype := xmltype (dbms_xmlquery.getxml (l_ctx));
  dbms_xmlquery.closecontext (l_ctx);

  dbms_output.put_line (l_xmltype.getclobval ());
end;
/
<?xml version = '1.0'?>
<Date>2012-07-13 22:45:25</Date>
PL/SQL procedure successfully completed.
Marked as Answer by Gert Poel · Sep 27 2020
Gert Poel
Thanks Michael.

Only this makes me still wonder why dbms_xmlgen does not follow the standard, while all other built-in packages do ;-)
odie_63
Only this makes me still wonder why dbms_xmlgen does not follow the standard, while all other built-in packages do ;-)
DBMS_XMLQUERY does not follow the standard either.

Does the previous output look like a W3C dateTime format?
--> No, it supports <tt>java.text.SimpleDateFormat</tt>, which can be set through SETDATEFORMAT procedure, which is not much different from doing an ALTER SESSION or an explicit TO_CHAR, IMO.

And DBMS_XMLQUERY is Java-based, Oracle recommends using DBMS_XMLGEN instead (C-based, builtin and faster).
1 person found this helpful
1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Apr 9 2018
Added on Mar 9 2018
#apex-5-1, #apex-discussions
6 comments
1,145 views