How to calculate first date of month from a given date in IDT BusinessObjects

[Former Member]

Hello Experts,

I have a date field in IDT Universe which shows date in this format: Dec 13,2014 12.00.00 PM. Now I want to extract first day and last day of month from this date value. For example: here I want to see Dec 01,2014 12:00:00 PM and Dec 31, 2014 12:00:00 PM in First day of month and Last day of month columns. Also, if database date value is Nov 17, 2014 12:00:00 PM then in first day of month column, it should show Nov 01, 2014 12:00:00 PM and in last day of month it should show Nov 30, 2014 12:00:00 PM.

I got some google help but 'Date' function is not supported by IDT tool in BusinessObjects.

Please help.

Thanks in advance.

[Former Member]

Create a function in your MYSQL database

DELIMITER ;;

CREATE FUNCTION FIRST_DAY(day DATE)

RETURNS DATE DETERMINISTIC

BEGIN

  RETURN ADDDATE(LAST_DAY(SUBDATE(day, INTERVAL 1 MONTH)), 1);

END;;

DELIMITER ;

You can then create an object as:

functionowner.FIRST_DAY(table.datecolumn);

where functionowner would be something like dbo

[Former Member]

Remi,

This is how you can get this.

First Day of Month: add_days(add_days(add_months(( current_date ),1),-extract(day from add_months(( current_date ),1))),-extract(day from add_days(add_months(( current_date ),1),-extract(day from add_months(( current_date ),1))) )+1)

Last Day of month:add_months(add_days(( current_date ),-extract(day from ( current_date ))),+1)

Replace current_date with your date field.

Thanks,

Haseeb

[former_member4998]

Hi

Try to use the DB Functions to find out the First and last day. Please find out the below screenshot.

If DB is BW, you can calculate the First and last day @ BW level use same object in BO directly.

SQL DB:

http://docs.oracle.com/html/A95915_01/sqfunc.htm#i1006893

Oracle DB:

http://msdn.microsoft.com/en-US/library/ms189794(v=sql.90).aspx

[Former Member]

Thanks Sreeni for reply.

I am using MySQL database to create connection in IDT Universe. When I checked in IDT to create new formula, it is not giving me 'lastday' function.

[Former Member]

If you created a multi-source data foundation you can create a derived table and use database specific syntax in your sql. MySQL has a LAST_DATE function.