Date Modifiction in the script

[mario-sarkis]

Dear ,

I need to put condition as following :

If(isnull([Field]),endofMonth(Month(PeriodDate),-1), PeriodDate) as PeriodDate

Example:

If the Field is null and my periodDate='43160' I need to replace it by '43159'

hope you can help.

Thank you,

[sunny_talwar]

May be this based on required condition

Date(If(Len(Trim(Field)) = 0 and PeriodDate = 43160, PeriodDate - 1, PeriodDate)) as PeriodDate

[sasikanth]

HI,

Use function MonthEnd,Addmonths

If(isnull([Field]),MonthEnd(AddMonths(PeriodDate),-1), PeriodDate) as PeriodDate

[shiveshsingh]

Date(if(len(Trim(Field))=0,Monthend(Addmonths(PeriodDate),-1),PeriodDate)) as PeriodDate

[sunny_talwar]

sasikanth_narne‌ & shiveshsingh‌ -

If you don't mind me asking, why do you need AddMonths here? You are not even giving any value for second paramater and MonthEnd() is handling going back one period... In fact, it would be advisable to add Floor() because MonthEnd() will give you a timestamp. So, if this is what the requirement is, I would use this

Date(If(Len(Trim(Field)) = 0, Floor(MonthEnd(PeriodDate, -1)), PeriodDate)) as PeriodDate

[shiveshsingh]

As per requirement(to counter this endofMonth(Month(PeriodDate),-1),, i put monthend before (Addmonths(PeriodDate),-1).

Monthend will not give timestamp, i guess.. or i missed this part.. I need to check.

Thanks

[sunny_talwar]

MonthEnd, YearEnd, WeekEnd functions always give a timestamp to the last second of the end date

So, MonthEnd(Today()) will give this

01/31/2018 23:59:59

[shiveshsingh]

Hi

Do i need to do some setting for timestamp, cz when i tried this in textbox (=Monthend(Today()) )

i got 1/31/2018

[sunny_talwar]

It displays date, but underlying is a TimeStamp. Adding TimeStamp() on top of MonthEnd() will show you that

But if you add floor, you will remove the time (as you would already know this)

[shiveshsingh]

Got it Sunny

Thanks