Find first and last date of a month?

Afternoon all,

Just a quickie, is there an expression built into Reporting Services from which you can find the first date and last date of any given month? The first date isn't much of a problem as it isn't quite as dynamic as the last date.

I can do it in an IIf statement but wouldn't be able to make it recognise 29th February in a leap year very easily.

Thanks,

Paul

Hello Paul,

If you have the first day of the month, you can just add a month to it and subtract a day.

=DateAdd("d", -1, DateAdd("m", 1, FirstDayOfMonth))

Hope this helps.

Jarret

|||

Hi Jarret,

Thanks for your reply, yes it helps a lot. Your solution is so simple it's brilliant.

Thanks,

Paul

|||

Phew,

Just thought I'd add this since I have proven that moving to SP2 9.0.3054 broke all my sub reports that passed a start and end date! (It blew up on the end date since the month and day were aways passed the wrong way round (i.e US when I'm en-GB)).

(No Language settings ANYWHERE fixed it, only code!)

Type in the "Code" section of report properties:

Public Function GetISODate(tmDate as DateTime)
return tmDate.Year & "-" & tmDate.Month & "-" & tmDate.Day
End Function

and use it on the navigation:
=Code.GetISODate(Parameters!<parameter>.Value)

It seems that the ISO formatted date (year-month-day) will be parsed the
same by every culture.

Credit goes to "Darren France", I just wished I'd assumed the DateTime object was in fact being passed as a date string!