Business Objects Insights from Dataspace

January 2009 e-newsletter

Business Objects provides date functions to get the current date, last day of a month, the number of days or months between two dates, but what if you want the first day of a month? Although there's not a function to automatically calculate/display the first day of a month, we can use a combination of functions and text to get the first day of a month.

In our report, we have a list of employees and their hire date.

Our company determines benefit eligibility based on the first day of the employee's hire date. So, we've got to create a variable to display this date.

We will first create variables that will give us the month and year of the hire date:

Name: Month of Hire Date
Syntax: = MonthNumberOfYear([Hire Date])

Name: Year of Hire Date
Syntax: =Year([Hire Date])

Next, we will create a new variable that will concatenate the month, 01 (for the first day of the month) and the year and we'll use a slash as the date separator.

Name: First Day of Hire Month
Syntax: =[Month of Hire Date]+"/01/"+[Year of Hire Date]

NOTE: The new variable, First Day of Hire Month, is a text field and not a date field. If you want to change the format or do additional date calculations on it, it will have to be converted to a date, as shown below:

Name: First Day of Hire Month Converted to a date
Syntax: =ToDate([First Day of Hire Month];"MM/dd/yyyy")

Now, it's a date and you can format it as needed or perform other date calculations.

Next month, you'll see how to do the same thing using the RelativeDate() function.

Founded in 1994, Dataspace is a consulting and solutions firm focused on the flawless execution of Business Intelligence and Data Warehousing initiatives. Visit www.dataspace.com.