Crystal formula to find the prior month end date from today

[jchewsmith]

What is the easiest way to write a formula to find the prior month end date?

I am trying to create a formula to find a "Fiscal year to date" when I am running the report in the middle of the next month.

So if I am running report on the 7/15 or 7/1, etc., I want the "Fiscal Year To date" to be 6/30

 

[bborissov]

DECLARE @Dt datetime
SET @dt = '20190719'

SELECT DATEADD(dd, -DAY(@dt), @dt)

Borislav Borissov
VFP9 SP2, SQL Server

 

[Andrzejek]

When does your Fiscal Year start and end?
I deal with two of them at my place. One starts 7/1 and ends on 6/30, and another one starts on 10/1 and runs until 9/30
So depending on which FY I deal with, I either add 6 or 3 months to any date, and retrieve a Year from that calculated data. And I have a FY for that date.


---- Andy

There is a great need for a sarcasm font.

 

[jchewsmith]

My fiscal year ends 6/30 but what I am trying to do is just get the month end date for the last full month.

I want my info to be for the Fiscal Year to date for the date ending the last full month.

So if today is May 15, I want data from 7/1 to 4/30
or if today is June 2, I want data from 7/1 to 5/31.

I have a formula for the fiscal year start date, I need a formula for the date of the last day of the month for the previous month.

 

[bborissov]

Did you try my example?

Borislav Borissov
VFP9 SP2, SQL Server

 

[jchewsmith]

I am looking for a crystal select formula, this appears to be a sql formula. How can I use this from within Crystal?

 

[Andrzejek]

You've asked your question here, at forum183, you've got the SQL Server answer.
Ask your question in one of the Crystal forums you belong to.


---- Andy

There is a great need for a sarcasm font.

 

[jchewsmith]

So Sorry! Thank you everyone for your help!