conversion failed when converting date from character string

  • 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

  • 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?

  • 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..

  • 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.

  • 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.

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

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