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'

Viewing 6 posts - 1 through 7 (of 7 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