Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Error when previewing a report with date parameters 'Conversion failed when converting datetime from character string' Expand / Collapse
Author
Message
Posted Tuesday, June 1, 2010 9:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 6:11 AM
Points: 4, 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
Post #930761
Posted Tuesday, June 1, 2010 9:53 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
Are the dates stored in a date type?

The format should not really matter as it is only used for display not storage
Post #930819
Posted Wednesday, June 2, 2010 4:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 6:11 AM
Points: 4, 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.
Post #931221
Posted Wednesday, June 2, 2010 4:21 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 6:11 AM
Points: 4, Visits: 7
Sorry, forgot to answer your question in my previous post . . .

the data type is set to nvarchar(10)
Post #931222
Posted Wednesday, June 2, 2010 4:28 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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.

Post #931224
Posted Wednesday, June 2, 2010 4:58 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, June 3, 2010 6:11 AM
Points: 4, Visits: 7
I have run the ISDATE function and all rows (30449) returned 1
Post #931238
Posted Wednesday, June 2, 2010 5:11 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, May 30, 2014 6:27 PM
Points: 2,808, Visits: 7,175
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
Post #931247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse