compare date with null and datetime values in sql server

Question

I am writing procedure in which i want to filter date using small date time from date and to date. from date can be null some times and To Date can also be null some time while comparing then how can i filter date when the from date or to date can be null.

I have tried following query but its giving me error.

 SELECT RQ.int_REPS_QUES_DIFF_LEVEL,SUM(1) AS NoOFDificultyQuestion FROM   
 REPS_TEST_QUES_ASSIGNED RQA   
 INNER JOIN REPS_QUESTION RQ ON RQA.int_REPS_TEST_QUES_ASSG_QUESID=RQ.PK_REPS_QUES_ID  
 WHERE int_REPS_TEST_ID IN(  
 SELECT FK_TEST_ID FROM STUDENT_EXAM SE WHERE FK_USER_ID=56 AND SE.FK_REPS_BATCH_ID=466 
    and CASE 
WHEN @FromDate!=NULL AND @ToDate!=NULL     
     THEN dat_STUD_EXAM_FINALEND >= @FromDate AND dat_STUD_EXAM_FINALEND <= @ToDate 
WHEN @FromDate!=NULL AND @ToDate=NULL  
     THEN  dat_STUD_EXAM_FINALEND >= @FromDate  
WHEN @FromDate=NULL AND @ToDate!=NULL  
     THEN  dat_STUD_EXAM_FINALEND <= @ToDate
END
 )   
 strong textGROUP BY RQ.int_REPS_QUES_DIFF_LEVEL

i am getting error at

THEN dat_STUD_EXAM_FINALEND >= @FromDate AND dat_STUD_EXAM_FINALEND <= @ToDate

this line please tell me where i am getting wrong

score 14 · Answer 1 · answered Oct 31 '13 at 08:08

14

Instead of

WHEN @FromDate!=NULL AND @ToDate!=NULL

use

WHEN @FromDate IS NOT NULL AND @ToDate IS NOT NULL

IS [NOT] NULL

If something is NULL it is undefined in T-SQL, so you cannot compare with it. Both = and != yield false if one of both (or both) values is NULL.

answered Oct 31 '13 at 08:08

Tim Schmelter

450,073
74
686
939

2

Note that you can `SET ANSI_NULLS OFF` to change the behavior of `=` so that it returns true when comparing any NULL value against NULL, and also make comparisons (`<`, `>=`, etc.) TRUE when comparing any non-NULL value to NULL. See [MSDN](https://msdn.microsoft.com/en-us/library/ms188048.aspx). Beware that this setting will be removed in a future version and its usage is definitely not recommended. – MarioDS May 26 '16 at 12:27

Giannis Paraskevopoulos · Answer 2 · 2013-10-31T09:31:29.057

Please format your queries. It is much better for reading.

SELECT  RQ.int_REPS_QUES_DIFF_LEVEL,
        SUM(1) AS NoOFDificultyQuestion 
FROM    REPS_TEST_QUES_ASSIGNED RQA   
        INNER JOIN REPS_QUESTION RQ 
            ON RQA.int_REPS_TEST_QUES_ASSG_QUESID=RQ.PK_REPS_QUES_ID  
WHERE   int_REPS_TEST_ID IN
        (  
            SELECT  FK_TEST_ID 
            FROM    STUDENT_EXAM SE 
            WHERE   FK_USER_ID=56 
                    AND SE.FK_REPS_BATCH_ID=466 
                    AND (
--If both dates are not NULL then also check dat_STUD_EXAM_FINALEND to be between them
                            (@FromDate IS NOT NULL AND @ToDate IS NOT NULL AND dat_STUD_EXAM_FINALEND >= @FromDate AND dat_STUD_EXAM_FINALEND <= @ToDate )
                            OR
--If @FromDate is not NULL AND @ToDate IS NULL then also check dat_STUD_EXAM_FINALEND to be greater than @FromDate
                            (@FromDate IS NOT NULL AND @ToDate IS NULL AND dat_STUD_EXAM_FINALEND >= @FromDate  )
                            OR
--If @FromDate is NULL AND @ToDate IS NOT NULL then also check dat_STUD_EXAM_FINALEND to be less than @ToDate 
                            (@FromDate IS NULL AND @ToDate IS NOT NULL AND dat_STUD_EXAM_FINALEND <= @ToDate )

--Having AND in each set makes it impossible two sets to be true at the same time. 
--So If both dates are not null the first set will be evaluated. 
--If @ToDate is NULL, then the first and third sets won't be evaluated as they need @ToDate to be not NULL
--If @FromDate is NULL, then the first and second sets won't be evaluated as they need @FromDate to be not NULL
                        )
        ) strongtext GROUP BY RQ.int_REPS_QUES_DIFF_LEVEL

compare date with null and datetime values in sql server

2 Answers2