CODEWITHSUNDEEP
reporting-servicesssrs-2008
enginocal
enginocal

Reputation: 319

Conversion failed when converting datetime from character string on reporting service

when i convert or cast string to date ran succesfull my query from t-sql on sql server but when i assign dataset query on reporting service,i take this error.

'Conversion failed when converting datetime from character string.'

Upvotes: 3

Views: 11998

Answers (3)

SamuraiCharlie
SamuraiCharlie

Reputation: 194

Make sure you are not forgetting to put the braces on the "parameter value" field when editing parameters on the DataSet Properties window:

This gives the conversion error:

Parameter Name        Parameter Value
@queryDateParameter   @reportDateParameter

Correct is:

Parameter Name        Parameter Value 
@queryDateParameter   [@reportDateParameter]

Upvotes: 4

savagepanda
savagepanda

Reputation: 877

I've had the same issue when trying to pass parameters through SSRS. when the parameter types are text, SSRS does a conversion using server region settings, in which case, a MM/DD/YYYY and DD/MM/YYYY difference resulted in this error.

Solution for me was to change the parameter type to datetime. This enforces the conversion to be handled correctly.

Upvotes: 2

Chris Latta
Chris Latta

Reputation: 20560

It will depend on the regional settings on your server. I guess your reporting services server thinks days and months are in the opposite position from your SQL server.

The easiest way to consistently get dates to convert from string properly without having to consider regional settings is to use military date format: YYYY-MM-DD

Military format always converts correctly.

Upvotes: 4

Related Questions