Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: How to get last day of a month Post Reply Post New Topic
Author Message
venky_115
Groupie
Groupie
Avatar

Joined: 15 Jun 2010
Location: United States
Online Status: Offline
Posts: 67
Quote venky_115 Replybullet Topic: How to get last day of a month
    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
IP IP Logged
Robotacha
Groupie
Groupie
Avatar

Joined: 11 Nov 2009
Location: United Kingdom
Online Status: Offline
Posts: 97
Quote Robotacha Replybullet 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 {@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 {@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


Edited by Robotacha - 05 Oct 2011 at 12:50am
Regards,

Michael Jones
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet 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
 
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
 


Edited by DBlank - 05 Oct 2011 at 8:10am
IP IP Logged
venky_115
Groupie
Groupie
Avatar

Joined: 15 Jun 2010
Location: United States
Online Status: Offline
Posts: 67
Quote venky_115 Replybullet Posted: 05 Oct 2011 at 9:58pm
Yes,solved. Thanks for your replies
Regards
IP IP Logged
Gurbs
Senior Member
Senior Member
Avatar

Joined: 16 Feb 2012
Location: Ireland
Online Status: Offline
Posts: 216
Quote Gurbs Replybullet 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
IP IP Logged
micalo
Newbie
Newbie


Joined: 28 Apr 2014
Location: Australia
Online Status: Offline
Posts: 3
Quote micalo Replybullet 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
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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)
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.