Print Page | Close Window

How to get last day of a month

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14587
Printed Date: 04 May 2024 at 8:24pm


Topic: How to get last day of a month
Posted By: venky_115
Subject: How to get last day of a month
Date Posted: 04 Oct 2011 at 9:15pm
Hi,

How to get last day of month, day(month).

like day(9)=30 or day(10)=31.. etc

Regards


-------------
Regards


Replies:
Posted By: Robotacha
Date Posted: 05 Oct 2011 at 12:49am

Im not sure if you can. I had a look through the fuctions and cant see one that relates to last day of month.

Im probably wrong and it probably can be done, in the meantime, take a look at these formulas I wrote (only this morning ironically). They may be able to help you with what you need.
 
@leapyear formula:
 
IF year(currentdate)=2012 THEN 29 ELSE
IF year(currentdate)=2016 THEN 29 ELSE
IF year(currentdate)=2020 THEN 29 ELSE
IF year(currentdate)=2024 THEN 29 ELSE
IF year(currentdate)=2028 THEN 29 ELSE 28
 
Days in month / Last day of month formula:
 
IF month(currentdate)=1 THEN 31 ELSE
IF month(currentdate)=2 THEN mailto:%7b@leapyear - {@leapyear } ELSE
IF month(currentdate)=3 THEN 31 ELSE
IF month(currentdate)=4 THEN 30 ELSE
IF month(currentdate)=5 THEN 31 ELSE
IF month(currentdate)=6 THEN 30 ELSE
IF month(currentdate)=7 THEN 31 ELSE
IF month(currentdate)=8 THEN 31 ELSE
IF month(currentdate)=9 THEN 30 ELSE
IF month(currentdate)=10 THEN 31 ELSE
IF month(currentdate)=11 THEN 30 ELSE
IF month(currentdate)=12 THEN 31
 
If you wanted to show the date of the last day of the month, then simply amend your formula to read:
 
IF month(currentdate)=1 THEN "31/01/"&year(currentdate) ELSE
IF month(currentdate)=2 THEN mailto:%7b@leapyear - {@leapyear } ELSE
IF month(currentdate)=3 THEN "31/03/"&year(currentdate) ELSE
IF month(currentdate)=4 THEN "30/04/"&year(currentdate) ELSE
IF month(currentdate)=5 THEN "31/05/"&year(currentdate) ELSE
IF month(currentdate)=6 THEN "30/06/"&year(currentdate) ELSE
IF month(currentdate)=7 THEN "31/07/"&year(currentdate) ELSE
IF month(currentdate)=8 THEN "31/08/"&year(currentdate) ELSE
IF month(currentdate)=9 THEN "30/09/"&year(currentdate) ELSE
IF month(currentdate)=10 THEN "31/10/"&year(currentdate) ELSE
IF month(currentdate)=11 THEN "30/11/"&year(currentdate) ELSE
IF month(currentdate)=12 THEN "31/12/"&year(currentdate)
 
@leapyear formula:
 
IF year(currentdate)=2012 THEN "29/02/2012" ELSE
IF year(currentdate)=2016 THEN "29/02/2016" ELSE
IF year(currentdate)=2020 THEN "29/02/2020" ELSE
IF year(currentdate)=2024 THEN "29/02/2024" ELSE
IF year(currentdate)=2028 THEN "29/02/2028" ELSE
"28/02/"&year(currentdate)
 
I also worked out the UK working days for the next 4 years:
 
//2011 working days
IF year(currentdate)=2011 and (month(CurrentDate)=1) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=3) then 23 else
IF year(currentdate)=2011 and (month(CurrentDate)=4) then 18 else
IF year(currentdate)=2011 and (month(CurrentDate)=5) then 20 else
IF year(currentdate)=2011 and (month(CurrentDate)=6) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=7) then 21 else
IF year(currentdate)=2011 and (month(CurrentDate)=8) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=10) then 21 else
IF year(currentdate)=2011 and (month(CurrentDate)=11) then 22 else
IF year(currentdate)=2011 and (month(CurrentDate)=12) then 20 else
//2012 working days
IF year(currentdate)=2012 and (month(CurrentDate)=1) then 21 else
IF year(currentdate)=2012 and (month(CurrentDate)=2) then 21 else
IF year(currentdate)=2012 and (month(CurrentDate)=3) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=4) then 18 else
IF year(currentdate)=2012 and (month(CurrentDate)=5) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=6) then 19 else
IF year(currentdate)=2012 and (month(CurrentDate)=7) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=8) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=9) then 20 else
IF year(currentdate)=2012 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2012 and (month(CurrentDate)=11) then 22 else
IF year(currentdate)=2012 and (month(CurrentDate)=12) then 19 else
//2013 working days
IF year(currentdate)=2013 and (month(CurrentDate)=1) then 22 else
IF year(currentdate)=2013 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=3) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=4) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=5) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=6) then 20 else
IF year(currentdate)=2013 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2013 and (month(CurrentDate)=8) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=9) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2013 and (month(CurrentDate)=11) then 21 else
IF year(currentdate)=2013 and (month(CurrentDate)=12) then 20 else
//2014 working days
IF year(currentdate)=2014 and (month(CurrentDate)=1) then 22 else
IF year(currentdate)=2014 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=3) then 21 else
IF year(currentdate)=2014 and (month(CurrentDate)=4) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=5) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=6) then 21 else
IF year(currentdate)=2014 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2014 and (month(CurrentDate)=8) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2014 and (month(CurrentDate)=10) then 23 else
IF year(currentdate)=2014 and (month(CurrentDate)=11) then 20 else
IF year(currentdate)=2014 and (month(CurrentDate)=12) then 21 else
//2015 working days
IF year(currentdate)=2015 and (month(CurrentDate)=1) then 21 else
IF year(currentdate)=2015 and (month(CurrentDate)=2) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=3) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=4) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=5) then 19 else
IF year(currentdate)=2015 and (month(CurrentDate)=6) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=7) then 23 else
IF year(currentdate)=2015 and (month(CurrentDate)=8) then 20 else
IF year(currentdate)=2015 and (month(CurrentDate)=9) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=10) then 22 else
IF year(currentdate)=2015 and (month(CurrentDate)=11) then 21 else
IF year(currentdate)=2015 and (month(CurrentDate)=12) then 21
 
 
Hope you, or anyone find these useful.
 
Regards,
 
mike


-------------
Regards,

Michael Jones

Posted By: lockwelle
Date Posted: 05 Oct 2011 at 8:00am
here's a simpler way...
 
local datevar aDate := (MONTH({table.field}), 1, YEAR({table.field}));
DateAdd("d", -1, aDate)
 
all that it is doing is getting the first day of the month, and then subtracting a day.  If you want the end of the current month, you could:
local datevar aDate := (MONTH({table.field}), 1, YEAR({table.field}));
aDate := DateAdd("m",1, aDate);   //you might need to use something besides "m"  look in CR help
DateAdd("d", -1, aDate)
 
HTH
 

Posted By: DBlank
Date Posted: 05 Oct 2011 at 8:08am
dateserial is also good for this if youa re only looking for the end of the current month or a table date field comparison:
// Currentmonth
dateserial(year(currentdate),month(currentdate)+1,1-1)
 
//datefield
dateserial(year(table.date),month(table.date)+1,1-1)
 

Posted By: venky_115
Date Posted: 05 Oct 2011 at 9:58pm
Yes,solved. Thanks for your replies

-------------
Regards

Posted By: Gurbs
Date Posted: 03 Apr 2013 at 2:07am
If you simply want the last day of the month, you could also use this formula:

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

This takes the first day of the next month, and then uses the day before it, so the last day of the current month

Posted By: micalo
Date Posted: 14 Dec 2014 at 2:55pm
Originally posted by Gurbs

If you simply want the last day of the month, you could also use this formula:

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

This takes the first day of the next month, and then uses the day before it, so the last day of the current month


This work well except if the last month is Decemeber but you could modify it to

IF month(currentdate)<>12 THEN
date(year(currentdate),month(currentdate)+1,1)-1
ELSE
date(year(currentdate)+1,month(currentdate)-11,1)-1

Posted By: DBlank
Date Posted: 15 Dec 2014 at 4:05am
yes that was not a good overall solution.
There are simpler solutions using dateserieal or datediff() and dateadd()
 
here is one example:
dateserial(year(currentdate),month(currentdate)+1,1-1)


Print Page | Close Window