BusinessObjects Board

Detail Object

building semantic layer
#1

Hello All,

I have one object Hdate which is Date and in MM/DD/YYYY format.I need to make three object.I am using Oracle.

Year, Quarter and Month out of this object.

Thanks

SH

SH3230 (BOB member since 2010-10-25)

#2

Hello SH,

I am using MySQL and in the select BOX i have used year(table/column) which is returning Year of that object.

I have not worked in Oracle.So, Some one will help you who is using oracle.

Thanks

Pooja

ktm :us: (BOB member since 2008-11-12)

#3

use the following oracle function

to_char(Hdate ,‘yyyy’) -> Year
to_char(Hdate ,‘q’)-> Quarter
to_char(Hdate ,‘mm’) -> Month

sati_sunil (BOB member since 2010-09-16)

#4

But remmember that those functions will create strings. If you need numbers you would have to apply an additional conversion.

Marfi :poland: (BOB member since 2006-12-18)

#5

Please refer to the sticky topic at the top of the Semantic Layer forum.

#6

You have several options:
Let’s say the object is Table.date
Month:

Trunc(table.date,'MM')
  • this will keep it in date format, and return the first day of the month (05/15/2010 will turn to 05/01/2010)
To_char(table.date,'MM/YYYY')
  • this will turn the object into char format (05/15/2010 will turn to 05/2010)

Quarter:

Trunc(table.date,'Q')
  • this will keep it in date format, and give the first day of the Quarter (05/15/2010 will turn to 04/01/2010)
To_char(table.date,'Q/YYYY')
  • this will turn the object into char format (05/15/2010 will turn to 2/2010)
    you might want to add the letter Q like this:
'Q'||To_char(table.date,'Q/YYYY')

so the result will be Q2/2010

Year:

Trunc(table.date,'YYYY')
  • this will keep it in date format, and return the first day of the year (so 05/15/2010 will turn to 01/01/2010)
To_char(table.date,'YYYY')
  • this will turn the object into char format (05/15/2010 will turn to 2010)

Dragoran :israel: (BOB member since 2009-07-30)