First/Last Day of Month (current and previous three months)

Hi,
I want to set a parameter to use the first and last days of the month for
two parameters' defaults. I then want to set the first day of the previous
two months as the "available values" for on of the parameters and the last
day of the previous two months for the other parameter's "available values".
Can anyone tell me why this isn't working (I want to get last day of month):
=DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
I have tried using the Today funcion instead of Now with no joy. I started
doing this with DATEADD and DATEDIFF, as is commonly found as the solution
for this on tons of tech articles. However, I have learnt that although
T-SQL can work out what you want to do with DATEADD (which expects DateTime)
and DATEDIFF(which returns an Integer), reporting services cannot. So I
found the above expression that doesn't use DATEDIFF, but I still can't get
it to work. The expression builder doesn't seem to find a problem with the
syntax (no green or red wavy lines), but when I run the report I get the
following:
An error occurred duing local report processing. Error during processing of
'SDate' report parameter.
Please tell me someone has an answer for me. This should be so simple. I
bet I am going to kick myself when (if!!) the answer comes through.
TIA,
JarrydHi,
Well I just did this in T-SQL as part of the procedure that feeds the
report. Created a virtual table and added a second dataset to the report.
That seems to work. One thing I find odd - the "calendar" button can't be
used to set the date properly. We are in the UK and so the calendar
generates a UK style date, but Reportiong Services won't have it; you have
to manually enter it in USA format!! How do you solve it? Please don't
tell me you have to programtically grab the variable and cast it in USA
style. That's just silly. But if so, then where do you do it? I am
assuming you have to go to Dataset>Parameters and jimmy the value field's
value (def: Parameters!My_Param.Value) to grab the value and reorder the
days and months but I can't get it to work. The only other place I know of
is the Report>Report Parameters form, but that doesn't look too promising.
TIA,
Jarryd
"Jarryd" <noemail@.nodomain.com> wrote in message
news:u4I1mEMqHHA.2372@.TK2MSFTNGP02.phx.gbl...
> Hi,
> I want to set a parameter to use the first and last days of the month for
> two parameters' defaults. I then want to set the first day of the
> previous two months as the "available values" for on of the parameters and
> the last day of the previous two months for the other parameter's
> "available values".
> Can anyone tell me why this isn't working (I want to get last day of
> month):
> =DATEADD("dd", - DAY(DATEADD("m", 1, Now)), DATEADD("m", 1, Now))
> I have tried using the Today funcion instead of Now with no joy. I
> started doing this with DATEADD and DATEDIFF, as is commonly found as the
> solution for this on tons of tech articles. However, I have learnt that
> although T-SQL can work out what you want to do with DATEADD (which
> expects DateTime) and DATEDIFF(which returns an Integer), reporting
> services cannot. So I found the above expression that doesn't use
> DATEDIFF, but I still can't get it to work. The expression builder
> doesn't seem to find a problem with the syntax (no green or red wavy
> lines), but when I run the report I get the following:
> An error occurred duing local report processing. Error during processing
> of 'SDate' report parameter.
> Please tell me someone has an answer for me. This should be so simple. I
> bet I am going to kick myself when (if!!) the answer comes through.
> TIA,
> Jarryd
>