×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Current Date = Last day of the month?
6

Current Date = Last day of the month?

Current Date = Last day of the month?

(OP)
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

RE: Current Date = Last day of the month?

Hi,

FIRST DAY of NEXT month minus 1.

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Current Date = Last day of the month?

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

RE: Current Date = Last day of the month?

More specifically:

CODE

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

RE: Current Date = Last day of the month?

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)

RE: Current Date = Last day of the month?

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

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

RE: Current Date = Last day of the month?

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

-LB

RE: Current Date = Last day of the month?

2
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/

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close