getting data between 2 date parameters

  • Hello,

    I have a query I am using in an ssrs report. It works great when run on the sql server. Once I put it in SSRS I get an error.

    I need to pass the parameter values to a temp table then retrieve the data which is 3 days prior to the start date and 3 days after the end date.

    i add this to SSRS and i get an error

    OPERAND TYPE CLASH: DATETIME2 IS INCOMPATIBLE WITH INT

    select DISTINCT

    F.PAT_PERSON_NUM,

    F.FINDNG_FAC_NUM,

    X.NMAS NAME,

    F.dttmAS DATE_DONE,

    F.TXTAS VALUE

    INTO #TMP_CRTN

    FROM PRISMSTAGE.dbo.findng f

    JOIN

    PRISMSTAGE.dbo.xcncpt x on x.num = F.CONCEPT_DESCR_NUM

    WHERE f.concept_descr_num =6106177

    AND F.dttm BETWEEN @startdate -3 AND @enddate +3

    how can i get these values ????

  • sharonmtowler (11/18/2016)


    Hello,

    I have a query I am using in an ssrs report. It works great when run on the sql server. Once I put it in SSRS I get an error.

    I need to pass the parameter values to a temp table then retrieve the data which is 3 days prior to the start date and 3 days after the end date.

    i add this to SSRS and i get an error

    OPERAND TYPE CLASH: DATETIME2 IS INCOMPATIBLE WITH INT

    select DISTINCT

    F.PAT_PERSON_NUM,

    F.FINDNG_FAC_NUM,

    X.NMAS NAME,

    F.dttmAS DATE_DONE,

    F.TXTAS VALUE

    INTO #TMP_CRTN

    FROM PRISMSTAGE.dbo.findng f

    JOIN

    PRISMSTAGE.dbo.xcncpt x on x.num = F.CONCEPT_DESCR_NUM

    WHERE f.concept_descr_num =6106177

    AND F.dttm BETWEEN @startdate -3 AND @enddate +3

    how can i get these values ????

    What is the data type for @startdate, @enddate and F.DTTM?

    What are @startdate and @enddate set to?

    Also, I would not use 'BETWEEN' with temporal data. Try:

    WHERE f.Concept_Descr_Num = 6106177

    and F.dttm >= DateAdd(dd, -3, @StartDate)

    and F.dttm < DateAdd(dd, +4, @EndDate)

    __________________________________________________________________________________________________________
    How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/

Viewing 2 posts - 1 through 1 (of 1 total)

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