Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Current Date = Last day of the month? 6

Status
Not open for further replies.

Bennie47250

Programmer
Nov 8, 2001
515
US
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
 
Hi,

FIRST DAY of NEXT month minus 1.

Skip,
[sub]
[glasses]Just traded in my OLD subtlety...
for a NUance![tongue][/sub]
 
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
 
More specifically:

Code:
Date(Year(CurrentDate),Month(CurrentDate)+1, 1)-1
 
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)
 
My supervisor came up with this years ago. I have never looked at it too closely.

Date(Year(Datadate),Month(Datadate),01)-1
 
I agree with Andy--use dateserial() to ensure correct results year round.

-LB
 
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top