How to calculate Start date of month or First date of Month in IDT

[former_member196948]

Hi All,

Could you please help me find/get start date of month/ first date of month in IDT

for eg today date is 15.05.2016 then output should be 01.05.206

I am using SAP BI 4.1 IDT

Thanks in Advance.

Jeet

[Former Member]

Hi pandey,

You can try this its working from our end we are using sql db .

Month First Day

CAST(DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE())-1,0)AS DATE)

Month Last Day

DATEADD(SS,86399,DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),0)-1)

Regards

Narasimhulu M

[former_member196948]

Hi

Thanks for your reply

I have tried trunc function but unable to achieve results

I am trying below Definition to achieve my requirement

(last_day( add_months(@Prompt(Sales Date),-1) )+ 1)

and @Prompt(Sales Date)

here sales date is Parameter, which i have created in IDT

Sales Date = CurrentDate()-1

I am testing above logic on my report.

Regards,

Jeet

[Former Member]

Hello Jeet,

Here's a blog below that should help you Netezza related date functions.

Some Date functions for Netezza

Regards

Niraj

[mhmohammed]

Hi Jeet,

Please let us know what the backend database is, that's critical for the solution.

For Oracle, the formula is trunc(datecolumn, 'MM')

Thanks,

Mahboob Mohammed

[former_member196948]

Hi Mohammed,

Trunc is not working here

Backend is netezza

Thanks

Ranjeet

[mhmohammed]

Hi Jeet,

Try this

--how to get first of the month given a date

select date_trunc('month', current_timestamp)

Thanks,

Mahboob Mohammed

[former_member196948]

Hi All,

Could you please help me find/get start date of month/ first date of month in IDT

for eg today date is 15.05.2016 then output should be 01.05.2016

I am using SAP BI 4.1 IDT

Thanks in Advance.

Jeet