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

Answer 1

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.

Answer 2

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