Geek asks and answers

WHERE clause returning invalid records

I use this

DECLARE @Year_Filter_Start AS DATETIME
SET @Year_Filter_Start = DATEADD( dd, -1, DATEADD( yy, DATEDIFF( yy, 0, GetDate() ), 0 ) )
DECLARE @Year_Filter_End AS DATETIME
SET @Year_Filter_End = GetDate()

INSERT INTO TABLE
  ( blah )
SELECT blah
  FROM OTHER_TABLE
 WHERE ACTISSUEDATE IS NULL 
    OR ACTSTARTDATE BETWEEN @Year_Filter_Start AND @Year_Filter_End

and it returns records where ACTISSUEDATE is not null and ACTSTARTDATE is not between the beginning of the year and today. @Year_Filter_Start should be earlier this year, @Year_Filter_End should be today.

For instance:

Record where ACTSTARTDATE is 2010-08-02 and ACTISSUEDATE is 2011-03-15

Or where ACTSTARTDATE is 2009-05-18 and ACTISSUEDATE is 2009-09-06

Is there something wrong with this statement?

+5
source share
7 answers

Try the following: you will need a few more brackets:

WHERE ((ACTISSUEDATE IS NULL)  
    OR (ACTSTARTDATE BETWEEN @Year_Filter_Start AND @Year_Filter_End))

The engine views this as two statements as you have. You need to do this.

+1
source

- , - , ACTISSUEDATE , ACTSTARTDATE . , ACTSTARTDATE , ACTISSUEDATE NULL.

, AND OR

0

where , True. , , , ACTISSUEDATE , , ACTSTARTDATE .

, OR WHERE AND.

0

, NULLS ( ).

:

WHERE ACTISSUEDATE IS NULL 
OR ACTSTARTDATE BETWEEN @Year_Filter_Start AND @Year_Filter_End

To:

WHERE ISNULL(ACTISSUEDATE, @Year_Filter_End) BETWEEN @Year_Filter_Start AND @Year_Filter_End
0

I see that you noted your questions on sql server 2005. Given that I wanted to indicate that the BETWEEN key is an endpoint, inclusive. Therefore, if you want only the beginning of the year, you must change the calculation of the date to

    DATEADD(yy, DATEDIFF(yy, 0, GetDate()), 0)

http://msdn.microsoft.com/en-us/library/ms187922.aspx

0
source

Where clause should be

WHERE TO BE ACCEPTED NO OR (ACTSTARTDATE BETWEEN @Year_Filter_Start AND @Year_Filter_End)

0
source

your request should be

WHERE ACTISSUEDATE is null 
    OR (ACTISSUEDATE is not null 
    and ACTSTARTDATE BETWEEN @Year_Filter_Start AND @Year_Filter_End)

hope it works ...

0
source

More articles:


All Articles