conversion failed when converting date from character string

Question

conversion failed when converting date from character string

soldout6000

Ten Centuries

Points: 1051
More actions
March 20, 2018 at 11:22 am

#329866

I have the following code. It is working fine in sql server. However, when I run it in ssrs, I'm getting an error "conversion failed when converting date and/or time from character string."
I'm using sql server as a data source in ssrs. I'm passing begin and end date values as 1/1/2017 and 9/30/2017
Please let me know what am I doing wrong here. Thank you. Any help would be greatly appreciated.
With rpt_date_range_view As(select cast(database1.dbo.get_begin_date((getdate() - @days),'date_range',@Begin_Date,@xDays) as date) Report_Begin_Date, cast(database1.dbo.get_end_date((getdate() - @days),'date_range',@End_Date,@xDays)as date) Report_End_Date)select *from rpt_date_range_view

Viewing 5 posts - 1 through 4 (of 4 total)

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

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

soldout6000 - Tuesday, March 20, 2018 11:22 AM
I have the following code. It is working fine in sql server. However, when I run it in ssrs, I'm getting an error "conversion failed when converting date and/or time from character string."
I'm using sql server as a data source in ssrs. I'm passing begin and end date values as 1/1/2017 and 9/30/2017
Please let me know what am I doing wrong here. Thank you. Any help would be greatly appreciated.
With rpt_date_range_view As(select cast(database1.dbo.get_begin_date((getdate() - @days),'date_range',@Begin_Date,@xDays) as date) Report_Begin_Date, cast(database1.dbo.get_end_date((getdate() - @days),'date_range',@End_Date,@xDays)as date) Report_End_Date)select *from rpt_date_range_view

Not enough information. How are the variables defined and set?, What are the functions doing? What are you expecting as output based on the values of the variables?

doug.brown SSCertifiable Points: 5608 More actions · Answer 2

Have you tried passing the dates in 2017-09-30 format instead of 9/30/2017? Depending on the language settings, SQL or SSRS could be interpreting the dates as DD/MM/YYYY..

soldout6000 Ten Centuries Points: 1051 More actions · Answer 3

Lynn, my begin_date and end_date parameters are defined as date/time datatypes in ssrs.
Doug.brown, I tried passing the dates in yyy-mm-dd format as well, but still getting the same error message about conversion failure from character string to date.
I think the problem is with ssrs date format not the code.
When I hard code the values for Begin_Date and End_Date in my query I don't get any error.
Please suggest as to what I could be doing here.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

soldout6000 - Friday, March 23, 2018 7:59 AM
Lynn, my begin_date and end_date parameters are defined as date/time datatypes in ssrs.
Doug.brown, I tried passing the dates in yyy-mm-dd format as well, but still getting the same error message about conversion failure from character string to date.
I think the problem is with ssrs date format not the code.
When I hard code the values for Begin_Date and End_Date in my query I don't get any error.
Please suggest as to what I could be doing here.

Take out the dashes and see what happens.