cancel
Showing results for 
Search instead for 
Did you mean: 

Finding the first day of the month in a date field

Former Member
0 Kudos

Hello there,

I am on BOBJ 4.0 and I have a requirement to calculate the beginning of the month for each date in a field. In other words, I have a "closed date field" which is displayed in the format 2013-10-18 18:13:00.0000000. Now for this value I want to add a calculation to say 2013-10-01. So for each value in the closed date field I want it to display the first day of that month. Is there a way to d this in Information Design Tool (Universe Designer) using DateAdd or any other functions? This is quite urgent. Any help would be much appreciated.


Thanks in advance!


Cheers,

Divya Rajnarain

drajnarain@us.ibm.com

Accepted Solutions (0)

Answers (1)

Answers (1)

arijit_das
Active Contributor
0 Kudos

Which Database are you using ? Based on the database syntax, it is possible to create new object in universe.

If it is SAP BW as backend, it is not possible to do any text and date related operations in universe.

Former Member
0 Kudos

Hi there,

The universe is built on 4.0 and reads from SFDC.

Thanks!

nscheaffer
Active Contributor
0 Kudos

There is a LastDayOfMonth function in Web Intelligence.  However, there isn't a FirstDayOfMonth function.  If there was you could just use that.  Instead create a new variable with the following formula...

=RelativeDate([Your Date Variable];(DayNumberOfMonth([Your Date Variable]) - 1) * -1)

Hope this help you out.

Noel

Former Member
0 Kudos

Thanks Noel! Is there any way to do it in the universe? Multiple reports are going to use this calculation. So I am trying to explore ways to get this calculation done in the universe.

Thanks!

nscheaffer
Active Contributor
0 Kudos

You certainly can do this at the universe level, but how you do that will be dependent on what database is being used as Arijit stated.  What is "SFDC"?

Noel

Former Member
0 Kudos

Divya,

Your data might be based on SFDC but what is the database the data is being stored in oracle/hana/sql server ??

Are you directly using the sales force connection to get data from salesforce cloud?

Former Member
0 Kudos

Noel,

SFDC is Salesforce.com CRM..

Former Member
0 Kudos

Noel,

I am using a universe that was originally based out of Salesforce data. Is it still possible to do this in the universe? If so, any suggestions of how my calculation should be?

Thanks!

Former Member
0 Kudos

That is going to be SQL Server.

Former Member
0 Kudos

check this in universe

DATEADD(month, DATEDIFF(month, 0, table.mydate), 0)

former_member188911
Active Contributor
0 Kudos

this is the easiest thing to do, there is only one day that is the first of the month, no month starts from the 2nd or the 5th day.

hence:

=ToDate("01"+"/"+month([MyDate])+"/"+year([MyDate]);"dd/MM/yyyy")

thanks

Simone