How to get the values present within 4 weeks / 28 days from the existing date in SQL Server

Question

From this table EmpRecord:

Name     |    JoiningDate      |     StartDate    
---------+---------------------+-----------------
  A           03/21/2017             05/25/2020 
  B           01/13/2020             01/29/2020
  C           04/07/2016             05/21/2020
  D           02/18/2020             02/29/2020

I need to fetch the result where the StartDate present within 4 weeks / 28 days from the JoiningDate where joining date should be within 1 year.

Expected results:

Name     |    JoiningDate      |     StartDate    
---------+---------------------+-----------------
  B           01/13/2020             01/29/2020   (16 days)
  D           02/18/2020             02/29/2020   (11 days)

I used this code but didn't get the expected results:

SELECT * 
FROM Datamining.dbo.EmpRecord
WHERE JoiningDate >= DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, GETDATE())), 1, 1)
  AND Startdate <= JoiningDate DATEADD(m, -6, GETDATE()) -- this returns wrong values

What should be derived in the Where clause to achieve the desired results?

question from:https://stackoverflow.com/questions/65888673/how-to-get-the-values-present-within-4-weeks-28-days-from-the-existing-date-in

Answer 1

You could use DATEDIFF() to determine the number of days between your StartDate and JoiningDate and evaluate if that is less than or equal to 28 days.

We'll use the ABS() function around DATEDIFF(). That gives us the absolute(positive) value to then evaluate against 28 days.

DECLARE @test TABLE
    (
        [name] CHAR(1)
      , [JoiningDate] DATE
      , [StartDate] DATE
    );

INSERT INTO @test (
                      [name]
                    , [JoiningDate]
                    , [StartDate]
                  )
VALUES ( 'A', '03/21/2017', '05/25/2020' )
     , ( 'B', '01/13/2020', '01/29/2020' )
     , ( 'C', '04/07/2016', '05/21/2020' )
     , ( 'D', '02/18/2020', '02/29/2020' )
     , ( 'E', '11/18/2020', '02/29/2020' );


SELECT *
FROM   @test
WHERE  [JoiningDate] >= DATEFROMPARTS(YEAR(DATEADD(YEAR, -1, GETDATE())), 1, 1)
       AND ABS(DATEDIFF(DAY, [StartDate], [JoiningDate])) <= 28;

Giving you results of:

name JoiningDate StartDate
---- ----------- ----------
B    2020-01-13  2020-01-29
D    2020-02-18  2020-02-29