Error when previewing a report with date parameters 'Conversion failed when converting datetime from character string'

Question

Post reply

Error when previewing a report with date parameters 'Conversion failed when converting datetime from character string'

nikkinax

Old Hand

Points: 386
More actions
June 1, 2010 at 9:02 am

#235730

I'm new to SSRS and would appreciate some assistance please.
I have created a dataset based on a view with a filter on the DateValueApplixFormat field in order to prompt for a StartDate and EndDate.
When I execute this query from the 'Data' tab, it prompts for the start and end date (which is in yyyy-mm-dd format) and gives me the required results.
I then configured StartDate and EndDate report parameters (datatype - DateTime, non-queried values with 'null' default values). When I preview the report, it prompts for the start and end date (which I select using the date picker). When I try to view the report, it errors with the following:
An error occurred during local report processing.
An error has occurred during report processing.
Cannot read the next data row for the data set DataWarehouse.
Conversion failed when converting datetime from character string.
Is this because the source data is in yyyy-mm-dd format where as when querying using the report parameter it it in dd/mm/yyyy?
The query the dataset is based on is:
SELECT nkCustomer, nkAccount, Currency, DateValueApplixFormat, ReversalIndicator, Amount, Narrative1, Narrative2, BeneficiaryName, nkTransaction
FROM vFactPaymentTransactionWithDatesAndHistory
WHERE (DateValueApplixFormat >= @StartDate) AND (DateValueApplixFormat < @EndDate + N'1')
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

Are the dates stored in a date type?

The format should not really matter as it is only used for display not storage

nikkinax Old Hand Points: 386 More actions · Answer 2

I have tried the convert function (to datetime) but then get an 'arithmetic overflow error converting expression to data type datetime'.

I have also amended the view to reference a different column which already has a data type of DateTime but still no joy (I get a similar error regarding 'conversion to int').

Thanks

N.

nikkinax Old Hand Points: 386 More actions · Answer 3

Sorry, forgot to answer your question in my previous post . . .

the data type is set to nvarchar(10)

steveb. SSC-Forever Points: 46733 More actions · Answer 4

Then thers is possibly data in that column that is not a true datetime and SQL will not be able to implicity convert this to a valid datetime.

Run a select on the table using ISDATE to find out what the offending data is.

nikkinax Old Hand Points: 386 More actions · Answer 5

I have run the ISDATE function and all rows (30449) returned 1

steveb. SSC-Forever Points: 46733 More actions · Answer 6

Then most likely the problem is as you stated in the first post, the different date formats are causing the issue.

I would run a select statement with a cast on the source data to put this into a datetime format