first day of previous month in crystal reports XI

[Former Member]

Hi,

I want to know the following SQL code in Crystal Reports XI syntax:

trunc(add_months(lbc_date,-1), 'MM')

this gives the first lbc_dates of the previous month.

Please help.

[Former Member · Accepted solution]

hi there,

please refer below :

Formula for the first and last day of a month:

Calculating the first of a target month is done differently depending on whether the target month is the current month or the month of a date field. If you are looking for the current month you can use:

Minimum ( MonthToDate )

This will give you the first day of the current month in any report. Not everyone realizes this but you can take the Minimum or Maximum of any one of the 27 date range functions Crystal provides. However, if the date is a database field, a formula or a parameter you need a different syntax. I use:

{Table.Date} - Day ( {Table.Date} ) + 1

The {Table.Date} field can be replaced with any Date value. If your field is a DateTime value you can add the DATE() function around the whole calculation to strip off the time:

DATE ( {Table.Date} - Day ( {Table.Date} ) + 1 )

Finding the last day of a month can be done several ways but I think the one below is the simplest. It can be used with any date value including the CurrentDate function:

DateAdd ('m' , 1 , {Orders.Order Date} - day({Orders.Order Date}) + 1) - 1

You will notice that the first formula is used within this one. I find the first of the target month, then use DateAdd to add one month and find the first of the following month. Finally I subtract 1 day to get the last day of the month. Note that DateAdd always returns a DateTime value. As above, you can use the DATE() function around this formula to strip off the time.

These calculations are especially handy when you need rolling date columns that are all derived from a parameter date. By adding and subtracting more months you can calculate the first and last date of a whole range of months.

Regards,

Clint

[former_member607473 · Accepted solution]

I tried this as a formula:

DateAdd ('m' , 1 , {Orders.Order Date} - day({Orders.Order Date}) + 1) - 1

I used CurrentDate as the variable and my result is 12/31/2013 - Today is Dec 13, 2013.

The answer doesn't seem to work for me.