failed to convert parameter value from DateTime to a Int32
Marty Cline
to get RS to understand the parsing out of a DateTime Field the values of a
month or year. I know the value I am comparing to is a number. I’ve tried
the following different ways:
= YEAR(@Date_Selection) – 1
= DatePart(yy,@Date_Selection) - 1
Additionally, I’ve tried casting and converting back and forth from and to
VarChar and Int to no avail.
Any thoughts?
--
Marty Cline
Alain Quesnel
and the whole query that's included in your SSRS dataset (including the
parameter).
Alain Quesnel
alains...@logiquel.com
"Marty Cline" <Marty...@discussions.microsoft.com> wrote in message
news:3B69E102-C30E-4FE9...@microsoft.com...
Marty Cline
This work perfectly in SQL server studio. Reporting services just has
issues with the datetime parameter.
Here's the sql:
DECLARE @DATE_Selection AS DATETIME
SET @DATE_Selection = '10/31/2007'
SELECT
CAST(CAST(RIGHT(DATEPART(YY,@DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@DATE_Selection) AS VarCHAR)AS INT) AS Start,
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) AS PriceMonth,
CASE WHEN CAST(CAST(RIGHT(DATEPART(YY,@DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) >13 THEN
CAST(CAST(RIGHT(DATEPART(YY,@DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) -88 ELSE
CAST(CAST(RIGHT(DATEPART(YY,@DATE_Selection),2) AS VarCHAR) +
CAST(DATEPART(MM,@DATE_Selection) AS VarCHAR)AS INT) -
CAST(CAST(RIGHT(YEAR,2) AS VarCHAR) +
LEFT(Convert(NCHAR,ir.ACCOUNTING_DATE,101),2)AS INT) END AS ReportOrder,
ir.ACCOUNTING_DATE, DATENAME(m, ir.ACCOUNTING_DATE) AS Month, DATENAME(d,
ir.ACCOUNTING_DATE) AS DAY, ir.PRICE, ir.YEAR,
td.Cusip, td.Curr_hold, sd.DESCRIPTION1, ir.IMPAIRMENT_LVL, ir.IMP_10_CNT,
ir.IMP_10_ST_DT, ir.COMPANY, ir.GAAP_BV, ir.MV, ir.PAR,
ir.GAAP_UNR_GL, sd.SEC_GROUP, sd.COUPON_RATE,
sd.MATURITY_DATE, td.Acquisition_date, ir.IMPAIRMENT_PCT
FROM Investments.CAM_IMPAIRMENT_ROLLUP AS ir INNER JOIN
Investments.CAM_TRAN_LOT_DIM AS td ON ir.CURR_HOLD =
td.Curr_hold INNER JOIN
Investments.CAM_SEC_DIM AS sd ON td.Cusip = sd.CUSIP
WHERE (1 = 1) AND (ir.YEAR = DATEPART(yy, @Date_Selection)) AND
(ir.MONTH < DATEPART(mm, @Date_Selection)) AND (ir.IMPAIRMENT_LVL <= 20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5) OR
(1 = 1) AND (ir.YEAR = DATEPART(yy, @Date_Selection) -
1) AND (ir.MONTH >= DATEPART(mm, @Date_Selection)) AND (ir.IMPAIRMENT_LVL <=
20) AND
(ir.IMPAIRMENT_LVL > 10) AND (ir.IMP_10_CNT > 5)
ORDER BY ir.COMPANY, td.Cusip, Accounting_Date
--
Marty Cline
Marty Cline
--
Marty Cline
Alain Quesnel
string the first time you create it, and if you modify it afterwards, it
tends to revert back to string. From your SQL code, it should be datetime.
If that's not the issue, you could try this:
LEFT(convert(varchar, @DATE_Selection, 2), 2)
instead of this:
CAST(CAST(RIGHT(DATEPART(YY,@DATE_Selection),2) AS VarCHAR)
And so on for the rest of your fields. You never know...
Alain Quesnel
alains...@logiquel.com
"Marty Cline" <Marty...@discussions.microsoft.com> wrote in message
news:F7CC4719-9007-4DFD...@microsoft.com...
Marty Cline
was set to DateTime.
I'm not sure why it was not working to begin with but it seems to be working
now. Probably something I did not know I did, but I'm not arguing. Thanks
for your help again!
Alain Quesnel
Debugging a report in SSRS based on the error message on the Preview page
isn't exactly intuitive. Or at least not in the beginning when you're not
familiar with the environment.
Alain Quesnel
alains...@logiquel.com
"Marty Cline" <Marty...@discussions.microsoft.com> wrote in message
news:491E40DE-D931-4162...@microsoft.com...