how to get first and last day (as datetimes) from a datetime value?

Hey,

In T-SQL I do this:
declare @.date datetime
set @.date = getdate()

--get first day of month
select dateadd(m, datediff(m, 0, @.date), 0)

--get last day of month
select dateadd(m, datediff(m, 0, dateadd(m, 1, @.date)), -1)

BUT when I do this is RS: for example the first day of month:

=dateadd("m", datediff("m", 0, Parameters!Date.Value), 0)

I get "#Error" displayed in the textbox. Also =datediff("m",0,Parameters!Date.Value) (the expression nested in the dateadd above) displayes the #Error message. So maybe that is the cause of failure for the whole expression.

Now my question is ... am I using the functions in RS in a wrong way? If not and it is not possible to retrieve the dates this way, is there another elegant way of doing so? (I know some ways of generating the wanted dates but they all are very messy)

Please help!

Grts

Here are some expressions that do what you are looking for. These use the DateTime methods on the actual object instead of using the VB functions.

For dermining the first day:
=Parameters!Date.Value.AddDays(-(Parameters!Date.Value.Day-1))

For dermining the last day:
=Parameters!Date.Value.AddMonths(1).AddDays(-(Parameters!Date.Value.Day))

Also, the error you were getting was most likely caused by not providing values that could be converted into DateTime objects to the DateDiff and DateAdd method. So, using these methods should work if you use DateTime objects instead 0 and -1, and convert the result of DateDiff to a DateTime.

Ian