Calculating First and Last day of current month?

[khan82]

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!!!

 

[IanWaterman]

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

Last day
dateadd("m", 1, Date(year(currentdate), month(currentdate), 01))-1

Ian

 

[khan82]

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

 

[SkipVought]

Try this...

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!}

 

[IanWaterman]

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

 

[SkipVought]

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!}

 

[khan82]

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

 

[SkipVought]

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

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}