Current Date = Last day of the month? 6

[Bennie47250]

Using Crystal Reports CD Developer version 14
Reporting on a DB2 table

Have an existing report that pulls shipment records for the last 7 days using this formula {Mytable.Myfield} >= CurrentDate -7

Now they would like this report to pull the full month shipment records only if the current date when the report runs is the last calendar day of the month. What would be the best way to determine if the current date is the last day of the month?

Thanks
Bennie5a

 

[SkipVought]

Hi,

FIRST DAY of NEXT month minus 1.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[AndyMc]

A formula for returning the last day of the month is:

dateserial(current_year_value,current_month_value+1,1-1)

(the first day of next month minus one day)

-Andy

 

[pmax9999]

More specifically:

Date(Year(CurrentDate),Month(CurrentDate)+1, 1)-1

 

[AndyMc]

pmax's formula will work unless it is December. If you change the function from date() to dateserial(),
it understands that month 12 + 1 is actually January of the next year and then when you subtract one day it know it is the previuos year again (great function).

Dateserial(Year(CurrentDate),Month(CurrentDate)+1, 1-1)

 

[kray4660]

My supervisor came up with this years ago. I have never looked at it too closely.

Date(Year(Datadate),Month(Datadate),01)-1

 

[lbass]

I agree with Andy--use dateserial() to ensure correct results year round.

-LB

 

[kenhamady]

I know I am a bit late here, but his question was actually "the best way to determine if the current date is the last day of the month?"
Which is slightly different from asking "what is the last date of the current month?"
So I would do it like this:

Month(CurrentDate+1) > Month(CurrentDate)​

When this is true you are on the last day of the month.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guides to Formulas, Parameters, Subreports, Cross-tabs, VB, Tips and Tricks

http://www.kenhamady.com/