Date format used in expression

I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
conversion function. I can't figure out how to do this in RS.
I have a couple of date parameters, used to filter by date range, with the
default beginning value set at 1/1/1900 and the end value at 12/13/2050.
What I want to be able to do in the report header (where I display the
parameter values that the user inputs at runtime), is have "All" appear when
the user chooses the default values (i.e. the user is filtering by "all"
dates for that range). If the user actually chooses dates, then I show those
dates (which has worked pretty easily with teh following:
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value)
So I am trying to build an expression to display this in the header. I've
tried:
=IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
I've also tried:
=IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) = 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value, vbShortDate)
= 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + " to
" + Parameters!CertRecordDtEnd.Value)
For the former, I got 2 errors:
- The value expression for the textbox contains an error: Input string was
not in a correct format, and
- The value expression for the textbox contains an error: [BC30108] 'Date'
is a type, and so is not a valid expression
For the latter, I don't get errors in my task list, but on the output, I get
"#Error" for that textbox.
Any ideas?
ThanksSee if any of these tips might help.
Case 1:
* Try using CDATE() or CSTR():
* CDATE(Parameters!CertRecordDtBegin.Value) // returns a Date type
* CSTR(DATE(1900,1,1)) // returns a string
* Use & instead of +
Case 2:
* Quote the dates to the right of the equals sign: "01/01/1900"
* Use & instead of +
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Derek in Richmond" <DerekinRichmond@.discussions.microsoft.com> wrote in
message news:4171551B-520F-4F8F-BA83-80FAAFF06252@.microsoft.com...
>I am an Oracle PL/SQL guy, and am used to formatting dates with a to_date
> conversion function. I can't figure out how to do this in RS.
> I have a couple of date parameters, used to filter by date range, with the
> default beginning value set at 1/1/1900 and the end value at 12/13/2050.
> What I want to be able to do in the report header (where I display the
> parameter values that the user inputs at runtime), is have "All" appear
> when
> the user chooses the default values (i.e. the user is filtering by "all"
> dates for that range). If the user actually chooses dates, then I show
> those
> dates (which has worked pretty easily with teh following:
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value)
> So I am trying to build an expression to display this in the header. I've
> tried:
> =IIf(Parameters!CertRecordDtBegin.Value = DATE(1900,1,1),
> IIf(Parameters!CertRecordDtEnd.Value = DATE(2050,12,31), "All",
> Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> I've also tried:
> =IIf(FormatDateTime(Parameters!CertRecordDtBegin.Value, vbShortDate) => 01/01/1900, IIf(FormatDateTime(Parameters!CertRecordDtEnd.Value,
> vbShortDate)
> = 12/31/2005, "All", Parameters!CertRecordDtBegin.Value + " to " +
> Parameters!CertRecordDtEnd.Value), Parameters!CertRecordDtBegin.Value + "
> to
> " + Parameters!CertRecordDtEnd.Value)
> For the former, I got 2 errors:
> - The value expression for the textbox contains an error: Input string was
> not in a correct format, and
> - The value expression for the textbox contains an error: [BC30108] 'Date'
> is a type, and so is not a valid expression
> For the latter, I don't get errors in my task list, but on the output, I
> get
> "#Error" for that textbox.
> Any ideas?
> Thanks
>