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: 19689
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 89 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: 19689
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 messageGo to next 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.
Re: date format [message #616574 is a reply to message #573715] Wed, 18 June 2014 08:01 Go to previous messageGo to next message
Rizwi
Messages: 11
Registered: June 2014
Location: DUBAI
Junior Member
As salamu alaikum,

I have problem one report..Report 6i run in excel fomat then show date format 17-Jun-14 but brother user want to show 17/Jun/2014. how it possible.


I have used RPT2XLS.put_cell(4, to_char(TO_date(SYSDATE),'DD/MM/RRRR'));

But Report display 17-Jun-14 format but user want to display 17/Jun/2014 Format...

Re: date format [message #616575 is a reply to message #616574] Wed, 18 June 2014 08:09 Go to previous message
cookiemonster
Messages: 11073
Registered: September 2008
Location: Rainy Manchester
Senior Member
I answered you here
Previous Topic: Oracle Report issue
Next Topic: Run Report 6i to Excel ..Date form 4/6/14 but i want 04/Jun/2014
Goto Forum:
  


Current Time: Wed Oct 22 22:08:46 CDT 2014

Total time taken to generate the page: 0.08729 seconds