×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• Best Of All, It's Free!

*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.

Calculating First and Last day of current month?

Calculating First and Last day of current month?

(OP)
I am designing a report and need a formula to calculate the first and last day of the current month as a selection criteria. can any body please help? I tried EOMONTH AND CURDATE but i am getting SQL error.

("PML_LEASE__LEASE_INSURANCE"."Expiration_Date">={d '2013-05-01'} AND "PML_LEASE__LEASE_INSURANCE"."Expiration_Date"<={d '2013-05-31'})

I want to automate the start and end date in the above expression.

Thanks!!!

RE: Calculating First and Last day of current month?

First of current month
Date(year(currentdate), month(currentdate), 01)

Last day

Ian

RE: Calculating First and Last day of current month?

(OP)
I tried the formula below instead and it worked too. Thanks!

first day of the month: datetimevar firstDate:= datetime(year(CurrentDate), month(CurrentDate),1,0,0,0)

last day of the month: dateadd("m",1,datetime(year(CurrentDate), month(CurrentDate),1,0,0,0))-1

RE: Calculating First and Last day of current month?

Try this...

CODE

First of current month
Date(year(currentdate), month(currentdate), 1)

Last of current month
Date(year(currentdate), month(currentdate) + 1, 0) 

Skip,

Just traded in my old subtlety...
for a NUANCE!

RE: Calculating First and Last day of current month?

Skip

This will not work when current month is December, also it will error out with error Day muts be number between 1 and last day of month (It fails in CR10, not sure if later versions accept this, but seems unlikely)

Last of current month
Date(year(currentdate), month(currentdate) + 1, 0)

You can use month(currentdate) + 1 with dateserial

Dateserial(year(currentdate), month(currentdate) + 1, 1) -1

Ian

RE: Calculating First and Last day of current month?

ahh.

Well silly me! I mistakenly thought that CR might behave like Excel & VB since I can make the arguments, at least the day argument zero or negative and the month argument can exceed 12 or be zeror or negative.

Oh well!

Skip,

Just traded in my old subtlety...
for a NUANCE!

RE: Calculating First and Last day of current month?

(OP)
i am confused now. will my formula not work? or should I be using the one Skip wrote?

RE: Calculating First and Last day of current month?

All it takes is trying something. It works or it doesn't!

Skip,

Just traded in my old subtlety...
for a NUANCE!

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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!