Home » Developer & Programmer » Reports & Discoverer » date format (reports 6i)
date format [message #573715] Wed, 02 January 2013 11:57 Go to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

I have a date column , and i want to show the date in 'DD/MM/YYYY' format for eg days with 2 digit or comming properly but when it comes to single digit the 0 is not comming for example 4/11/2012 should come as 04/11/2012 like 16/11/2012 , i even did the conversion like to_char(rh_dt,'DD/MM/RRRR').
Re: date format [message #573716 is a reply to message #573715] Wed, 02 January 2013 12:03 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Any chance for you to provide test case? On my 11g XE it works OK:
SQL> create table test (id number(1), datum date);

Table created.

SQL> insert all
  2    into test (id, datum) values (1, date '2012-12-31')
  3    into test (id, datum) values (2, date '2013-08-02')
  4  select * from dual;

2 rows created.

SQL> select id, to_char(datum, 'dd.mm.yyyy') dat
  2  from test;

        ID DAT
---------- ----------
         1 31.12.2012
         2 02.08.2013

SQL>
Re: date format [message #573738 is a reply to message #573716] Wed, 02 January 2013 22:22 Go to previous messageGo to next message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks littlefoot, please find below the test case , but the problem is in report as when i am importing this query unsing rpt2xls from reports to excel, the format changes to 6/11/2012.I am attaching the screenshot for you.I added the code in the format trigger as well but its not working.


SQL> CREATE TABLE OT_REQQ(RH_NO NUMBER,RH_DT DATE);

Table created.

SQL> INSERT INTO OT_REQQ VALUES (1,'04-DEC-2012');

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM OT_REQQ;

     RH_NO RH_DT
---------- ---------
         1 04-DEC-12

--I changed the format trigger as below.

begin
 RPT2XLS.PUT_CELL(1,TO_CHAR(TO_DATE(:rh_dt),'DD/MM/RRRR') );
 RPT2XLS.NEW_LINE;
  return (TRUE);
end; 




  • Attachment: rep.PNG
    (Size: 19.52KB, Downloaded 53 times)

[Updated on: Wed, 02 January 2013 22:32]

Report message to a moderator

Re: date format [message #573751 is a reply to message #573738] Thu, 03 January 2013 00:10 Go to previous messageGo to next message
Littlefoot
Messages: 18833
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I never worked with RPT2XLS.

RH_DT column is a date. When you select it in your report, it is still a date, so there's no point in TO_DATE(:RH_DT) again. TO_CHAR does its job correctly, I believe (which you would see if you run the report normally, from Reports Builder).

According to the screenshot you attached, I'd say that it is Excel to blame, not Oracle. OR: if it is Oracle, then investigate in RPT2XLS, whether it contains some options which would tell Excel what format to apply to that cell (actually, the whole column that contains date value).
Re: date format [message #573755 is a reply to message #573751] Thu, 03 January 2013 00:42 Go to previous message
arif_md2009
Messages: 732
Registered: May 2009
Location: United Arab Emirates
Senior Member

thanks littlefoot, i too believe it has do with rpt2xls tool.Well thanks again for your valuable help.
Previous Topic: editing margin in oracle reports 10g
Next Topic: REP3335: Unhandled Internal CA Error depep 2!
Goto Forum:
  


Current Time: Mon Apr 21 01:26:40 CDT 2014

Total time taken to generate the page: 0.08094 seconds