OraFAQ Forum: Business Intelligence

Home » Fusion Middleware & Colab Suite » Business Intelligence » Date Formatting

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date Formatting [message #612109]

Sat, 12 April 2014 02:49

vipul.gupta2
Messages: 6
Registered: April 2014
Location: India

Junior Member

I have Date in YYYY-MM-DD format. I want to print date in different Block
For example Year 2014 must be print as 2 0 1 4 in four different fields. same for the month and day also.

Report message to a moderator

Re: Date Formatting [message #612110 is a reply to message #612109]

Sat, 12 April 2014 02:52

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If these "date" values are stored in a DATE column, use TO_CHAR with appropriate format mask in order to convert it to a character string. Then use SUBSTR function to extract every digit. For example:

SQL> with dates as
  2    (select to_char(sysdate, 'yyyy') c_year from dual)
  3  select substr(c_year, level, 1) c_digit
  4  from dates
  5  connect by level <= 4;

C
-
2
0
1
4

SQL>

Report message to a moderator

Re: Date Formatting [message #612112 is a reply to message #612110]

Sat, 12 April 2014 03:08

vipul.gupta2
Messages: 6
Registered: April 2014
Location: India

Junior Member

thanks for your reply, but can i handle this functionality ib BI Publisher Code...??? Is there any way to do it...??

Report message to a moderator

Re: Date Formatting [message #612114 is a reply to message #612112]

Sat, 12 April 2014 03:11

Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

I don't know, I don't use BI Publisher. I *hope* it supports SUBSTR, and that's all you need. You'd just rewrite it as

select substr(c_year, 1, 1) first_digit,
       substr(c_year, 2, 1) second_digit, 
       substr(c_year, 3, 1) third_digit,
       substr(c_year, 4, 1) fourth_digit
from your_table

or even

first_digit  := substr(c_year, 1, 1);
second_digit := substr(c_year, 2, 1);
etc.

[Updated on: Sat, 12 April 2014 03:12]

Report message to a moderator

Previous Topic:	OBIEE Web Services
Next Topic:	Format Data for a Custom Group - Oracle BI

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Feb 09 02:24:54 CST 2025