Home » Fusion Middleware & Colab Suite » Business Intelligence » Date Formatting
Date Formatting [message #612109] Sat, 12 April 2014 02:49 Go to next message
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.
Re: Date Formatting [message #612110 is a reply to message #612109] Sat, 12 April 2014 02:52 Go to previous messageGo to next message
Littlefoot
Messages: 21806
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>
Re: Date Formatting [message #612112 is a reply to message #612110] Sat, 12 April 2014 03:08 Go to previous messageGo to next message
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...??
Re: Date Formatting [message #612114 is a reply to message #612112] Sat, 12 April 2014 03:11 Go to previous message
Littlefoot
Messages: 21806
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:
  


Current Time: Fri Mar 29 05:04:56 CDT 2024