none
SSRS dates are nvarchar in db and i need it to work with parameter date

    Question

  • dates are nvarchar in db and i need it to work with parameter date

    This work if i hardcode the date

    WHERE date >= '2014-06-28'

    Here is what i want to have

    WHERE date >= @startdate AND date <= @enddate

    But this does not work.

    Systemdeveloper @ 4film



    Friday, August 29, 2014 6:46 AM

Answers

  • Hi ,

    Try with this .

    WHERE cast (date as datetime) >= @startdate AND
    cast (date as datetime) <= @enddate

    Thanks

    Friday, August 29, 2014 7:16 AM
  • Hello Edvard,

    converting string to dat using CAST depend on the user Setting, it can fail. Better use CONVERT with the date Format Parameter instead, in this case Format 120 = ODBC =>

    WHERE CONVERT(datetime, date, 120) BETWEEN @startdate AND @enddate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 7:42 AM
  • Hi Edvard,

    According to your description, you want to use parameter @startdate and @enddate as search condition, when you set parameter value, data meet the condition will be displayed.

    According to my test, Olaf’s solution works in SSRS 2008. Since there is no value returns when you use where isDate(date) = 0, it means the date format is valid. To achieve your goal, please try to add a filter to the dataset. For more information, please refer to the following steps:

    1. Right-click the dataset used to retrieve data and click and click Dataset Properties.
    2. In left pane, click Filters, click Add,  a new blank filter equation appears.
    3. In Expression, click the expression (fx) button, then type the expression below:

    =FormatDateTime(Fields!date.Value,DateFormat.ShortDate)

    4. From the drop-down box, select Date/Time in the expression you created in step 3.
    5. In the Operator box, select Between.
    6. In the Value box, type the expression or value against which you want the filter to evaluate the value in Expression.
    7. Click OK. 


    The following screenshot is for your reference:

     

    If you have any questions, please feel free to let me know.

    Best Regards,
    Wendy Fu

    Tuesday, September 02, 2014 5:57 AM
    Moderator

All replies

  • Hi ,

    Try with this .

    WHERE cast (date as datetime) >= @startdate AND
    cast (date as datetime) <= @enddate

    Thanks

    Friday, August 29, 2014 7:16 AM
  • This is what happened


    Systemdeveloper @ 4film


    Friday, August 29, 2014 7:26 AM
  • Hello Edvard,

    converting string to dat using CAST depend on the user Setting, it can fail. Better use CONVERT with the date Format Parameter instead, in this case Format 120 = ODBC =>

    WHERE CONVERT(datetime, date, 120) BETWEEN @startdate AND @enddate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 7:42 AM
  • Hello Edvard,

    converting string to dat using CAST depend on the user Setting, it can fail. Better use CONVERT with the date Format Parameter instead, in this case Format 120 = ODBC =>

    WHERE CONVERT(datetime, date, 120) BETWEEN @startdate AND @enddate


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Still Same problem


    Systemdeveloper @ 4film

    Friday, August 29, 2014 8:14 AM
  • Seems you have non-valid date values in your table. Which Version of SQL Server are you using? If it's 2012 or 2012 then you can use the TRY_CONVERT (Transact-SQL) function

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 8:39 AM
  • Seems you have non-valid date values in your table. Which Version of SQL Server are you using? If it's 2012 or 2012 then you can use the TRY_CONVERT (Transact-SQL) function

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Sorry no, running on 2008

    Systemdeveloper @ 4film

    Friday, August 29, 2014 8:44 AM
  • Then check you data for invalid date values with ISDATE (Transact-SQL)

    SELECT *
    FROM yourTable
    WHERE ISDATE(date) = 0


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, August 29, 2014 8:52 AM
  • What format are your dates stored in?  Can you post a couple of examples?  And do all records use the same format?

    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence

    Friday, August 29, 2014 9:06 AM
  • you are actually using other than datetime value ..  please check data accuracy for datetime column.
    Friday, August 29, 2014 9:07 AM
  • These are the dates in the table


    Systemdeveloper @ 4film

    Friday, August 29, 2014 9:21 AM
  • As Olaf mentioned earlier, please use the ISDATE function to check if you've got any values that don't comply with the format.  Just one record is sufficient to cause that error.


    SQL Server MVP, MCITP/MCTS SQL Server 2008
    Check out my articles at BI: Beer Intelligence


    Friday, August 29, 2014 9:33 AM
  • It runs but i dont get any values

    This it with the WHERE ISDATE(date) = 0


    Systemdeveloper @ 4film


    Friday, August 29, 2014 9:37 AM
  • Hi Edvard,

    According to your description, you want to use parameter @startdate and @enddate as search condition, when you set parameter value, data meet the condition will be displayed.

    According to my test, Olaf’s solution works in SSRS 2008. Since there is no value returns when you use where isDate(date) = 0, it means the date format is valid. To achieve your goal, please try to add a filter to the dataset. For more information, please refer to the following steps:

    1. Right-click the dataset used to retrieve data and click and click Dataset Properties.
    2. In left pane, click Filters, click Add,  a new blank filter equation appears.
    3. In Expression, click the expression (fx) button, then type the expression below:

    =FormatDateTime(Fields!date.Value,DateFormat.ShortDate)

    4. From the drop-down box, select Date/Time in the expression you created in step 3.
    5. In the Operator box, select Between.
    6. In the Value box, type the expression or value against which you want the filter to evaluate the value in Expression.
    7. Click OK. 


    The following screenshot is for your reference:

     

    If you have any questions, please feel free to let me know.

    Best Regards,
    Wendy Fu

    Tuesday, September 02, 2014 5:57 AM
    Moderator