Last Day Of Month 2

[jason8643]

I'm attempting to only pull back the last day of month in a recordset for each month for the entire year. Does anyone know if there is a simple formula to make Crystal retrieve this?


thanks!

 

[dgillz]

The following will return the date of the last day of the month:


If month({DateField}) in [1,3,5,7,8,10,12] then date(year(DateField}), month({Datefield}),31) else

If month({DateField}) in [4,6,9,11] then date(year(DateField}), month({Datefield}),30) else

If remainder(year({DateField}),4)=0 then date(year(DateField}), month({Datefield}),29) else
//above handles leap years

date(year(DateField}), month({Datefield}),28) else

If this is not what you are looking for let me know.



Software Sales, Training, Implementation and Support for Exact Macola, eSynergy, and Crystal Reports

http://www.trianglepartners.com

 

[bdreed35]

This will work for you as well:

DateAdd("d",-1,Date(Year(DateAdd("m", 1, {TABLE.DATE})),Month(DateAdd("m", 1, {TABLE.DATE})),1))

~Brian

 

[synapsevampire]

Or there's always:

dateadd("M",1,cdate(year(currentdate),month(currentdate),1))-1

-k

 

[synapsevampire]

Oooops, missed your requirement there about the recordset, replace currentdate with your field:

{table.date} = dateadd("M",1,cdate(year({table.date}),month({table.date}),1))-1

This won't be extremely efficient from within Crystal, so consider building out this formula in a SQL Expression, and then filtering where {table.date} = {%lastdateofmonth}

That way the database can filter it.

-k