SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


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


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

Author
Message
nikkinax
nikkinax
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 7
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
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10764 Visits: 7195
Are the dates stored in a date type?

The format should not really matter as it is only used for display not storage
nikkinax
nikkinax
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 7
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
nikkinax
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 7
Sorry, forgot to answer your question in my previous post . . .

the data type is set to nvarchar(10)
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10764 Visits: 7195
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
nikkinax
SSC Journeyman
SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)SSC Journeyman (88 reputation)

Group: General Forum Members
Points: 88 Visits: 7
I have run the ISDATE function and all rows (30449) returned 1
steveb.
steveb.
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10764 Visits: 7195
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search