Compare date with null and datetime values ​​in sql server

Question

I am writing a procedure in which I want to filter the date using a short time from the date to the date. from the date can be null several times, and To Date can also be null for a while when comparing, then how can I filter date when the date or date can be empty.

I tried the following query, but it gave me an 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 get an error when

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

this line please tell me where am i wrong

Answer 1

Instead

 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 != Give false if one of the two (or both) values ​​is NULL .

Answer 2

Please format your queries. It is much better to read.

 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