how to get First day and Last day of the month

system · November 23, 2008, 7:32pm

Hi,
How want to derived First Day of the Month and Last day of the Month using date filed? For example, I have 05/17/2008 in date format and I want to get 05/01/2008 and 05/31/2008 from that date.
I am using Crystal Report XIR2.
Thanks

wack_jack (BOB member since 2005-12-28)

system · November 23, 2008, 9:36pm

I can’t answer the question of how to do this within Crystal, but normally in the BO world, this would be done at the universe level using the database to generate the dates that you want.

I would suggest that you do a search in either the Designer or Crystal forums and you will find the information that you want.

plessiusa (BOB member since 2004-03-22)

system · November 24, 2008, 3:10pm

You could do this in the same way with Crystal is you know the right database functions. You would use an SQL Expression field to get the value in Crystal though. Depending on the database there are usually functions for deriving such values from a date.

Or you could write a Crystal function to do it, not sure of the code though without writing it myself. Although working out the true first day of the month is easy, just replace the day number with 1. The last day of the month is just a case of detecting which month your in and adding 28,29,30 or 31 depending on what month. Although your difficulty then comes in detecting if your in a leap year…!

ABILtd (BOB member since 2006-02-08)

system · November 24, 2008, 4:26pm

create a formula.
In the formula workshop, on the left, click on REPOSITORY CUSTOM FUNCTIONS.
Under it click on CRYSTAL, and then on DATE.
In the list RIGHT CLICK on CDFIRSTDAYOFMONTH and click on ADD TO REPORT.
RIGHT CLICK on CDLASTDAYOFMONTH and click on ADD TO REPORT.
Go to your new formula and in the formula workshop window, in the FUNCTIONS window, expand until you see CUSTOM FUNCTIONS at the very end.
You should see CDFIRSTDAYOFMONTH & CDLASTDAYOFMONTH functions. Expand each and double click CDFIRSTDAYOFMONTH to add to the formula workshop.
Modify the formula to:

CDFIRSTDAYOFMONTH({DATE.FILED});

Repeat step 7 to add CDLASTDAYOFMONTH and modify it thus:

CDLASTDAYOFMONTH({DATE.FILED});

That will give you the first and last day of date filed. You might want to create a separate formula for each.

Sanjay Kodidine (BOB member since 2008-03-19)

system · November 25, 2008, 1:39am

Thanks to all for their reply,
I am looking report level function. For example, there is LastDayOfMonth() function in WebI which gives last day of the month easily.
SKodidine, I don’t see anything under Repository Custom Functions in Formula Workshop. Did you created those functions somewhere in BO Repository? if yes, how to create those functions and make them available in Crystal Report.
Thank you.

wack_jack (BOB member since 2005-12-28)

system · November 25, 2008, 1:49am

A question that you need to ask yourself is.

Will it be possible for this formula that I want to be used again in another report?

If the answer is no or not likely, then build it in the report.
If the answer is yes or quite likely, then build it in the universe.

plessiusa (BOB member since 2004-03-22)

system · November 25, 2008, 3:32am

You can do this in Crystal formula’s if necessary.
create two formula’s:

@First of Month
Date (Year ({Orders.Order Date}), Month ({Orders.Order Date}), 01)

@Last of Month
numberVar mth := if Month ({Orders.Order Date}) = 12
then 1 else Month ({Orders.Order Date}) + 1;
numberVar yr := if mth = 1 then yr := Year ({Orders.Order Date}) + 1
else Year ({Orders.Order Date});
Date (yr, mth, 01) - 1

blair (BOB member since 2006-11-17)

system · November 28, 2008, 2:36pm

After you get @FirstofMonth how about:

dateadd(“m”, 1, {@FirstofMonth})-1

samiam07 (BOB member since 2008-02-04)

system · December 1, 2008, 8:47am

dFirstDayOfMonth: Date(Year(currentdate),Month(currentdate),1)
dLastDayOfMonth: Date(DateAdd(“d”,-1,DateAdd(“m”,1,{@dFirstDayOfMonth})))

enfice4ever (BOB member since 2008-12-01)

system · June 5, 2012, 3:29pm

Taking some of the posts below, this is how I got it to work.

Step 1 - Create a Formula field titled Current Year. Add the below formula to it.
Year ({@Todays Date})

Step 2 - Create a Formula field titled Current Month. Add the below formula to it.
Month ({@Todays Date})

Step 3 - Create a Formula field titled Date of Previous Month Beginning. Add the below formula to it.
//Determine the first “date” of the previous month, for example 06/01/2012
if {@Current Month} = 12 then
Date ({@Current Year}-1,{@Current Month} - 11 ,1 )
else
Date ({@Current Year},{@Current Month} - 1 ,1 )

Step 4 - Create a Formula field titled Date of Previous Month Ending. Add the below formula to it.
//Determine the last “date” of the previous month, for example 06/30/2012
Date(DateAdd(“d”,-1,DateAdd(“m”,1,{@Date of Previous Month Beginning})))

Add the Beginning and Ending formula fields to your record selection. You may need to adjust for UTC values depending on how your database stores them.

awiggans (BOB member since 2012-06-05)

system · June 7, 2012, 3:12pm

If the data for your report is coming from tables you’ve linked together in the report and not from a stored procedure or a command, you could also create a SQL Expression to have the database do the work. In Oracle, these would look something like this:

First of Month: trunc(“MYTABLE”.“DATE_FIELD”, ‘MM’)
End of Month: add_months(trunc(“MYTABLE”.“DATE_FIELD”, ‘MM’), 1) -1

-Dell

hilfy (BOB member since 2007-04-16)

system · June 11, 2012, 3:08pm

Alternatively, you could use the LAST_DAY() function in Oracle.

zrome (BOB member since 2012-06-11)

system · June 6, 2013, 4:30pm

This setup worked perfectly for me. Thanks!!

zacharybrown (BOB member since 2013-06-06)