BusinessObjects Board

Month number and day number from date

building semantic layer
#1

Hi guys
what is the formula I can use to get the month number and day number from a date? I need to have a calculated column in my universe layer…

thanks

nutty (BOB member since 2015-10-19)

DB-specific Relative Dates SQL Server, Oracle, Netezza, DB2
#2

What is your data source?

#3

Microsoft SQL server 2012 database

nutty (BOB member since 2015-10-19)

#4

Have a look at MSDN Books Online about the datepart function:

If you create objects with getdate() then they won’t parse but you can still use them and they will work as long as you have one table-based object in your query.

#5

this does not work :frowning:

here is how I am using it in the universe layer:

(case 
when @catalog('EDW')."SDDWPR01.dbo"."DIM_BILLING_GROUP"."MASTER_GROUP_COMMON_BILL_DATE" is null 
      then datepart(mm,todate(@catalog('EDW')."SDDWPR01.dbo"."DIM_POLICY"."BILL_EFFECTIVE_FROM_DATE")) 
else @catalog('EDW')."SDDWPR01.dbo"."DIM_BILLING_GROUP"."MASTER_GROUP_COMMON_BILL_DATE" 
end)

and also, the universe is multi-source

[Moderator Edit: Added code formatting - Andreas]

nutty (BOB member since 2015-10-19)

#6

Makes all the difference!

My one attempt in a multi-source oracle/SQL Server UNX - and the code required for objects was a little … unique! I cobbled together what I needed from this site:

Debbie

Debbie :uk: (BOB member since 2005-03-01)