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!

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

Jobs

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
dateadd("m", 1, Date(year(currentdate), month(currentdate), 01))-1

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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,

glassesJust traded in my old subtlety...
for a NUANCE!tongue

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!

Resources

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