My date filter is not working properly in SQL Server

This is the view I am using to retrieve the data in my UI. The problem with this is that when I look data between 2021-03-09' and '2021-03-17, it returns the result from 2021-03-18 as well.

I had look into Postman to check what date it is passing through and found that it sending date like startDateTime%20ge%202021-03-08T12:00:00.000Z%20and%20startDateTime%20le%202021-03-17T12:00:00.000Z which returns 2021-03-18 data as well. I do not want it to return the 18th one. Is there any way to modify in the script. But If search for 2021-03-18 date range like startDateTime%20ge%202021-03-17T12:00:00.000Z%20and%20startDateTime%20le%202021-03-18T12:00:00.000Z, it returns correct number of row.

ALTER VIEW [dbo].[vw_o_sessions]  AS      SELECT DISTINCT          NEWID() AS TUniqueKey,          SNumber AS Sessionid,          CSession,          SStartDateTime AS StartDateTime,      FROM          [vw_o_appointments]      GROUP BY          SSessionNumber,               CSession,           SStartDateTime,   

The other view:

ALTER VIEW [dbo].[vw_o_appointments]  AS      SELECT          NEWID() AS TUniqueKey,          CASE              WHEN (PE.EAdmitDateTime IS NOT NULL                    AND CAST(PE.EAdmitDateTime AS time) < '12:30') OR                    (CAST(PE.ADate AS time) < '12:30')                THEN CAST(CONVERT(varchar, ISNULL(Pe.EAdmitDateTime, PE.ADate),  112) AS varchar(50)) + '0'             WHEN (PE.EAdmitDateTime IS NOT NULL                    AND CAST(PE.EAdmitDateTime AS time) >= '12:30')                   OR (CAST(PE.ADate AS time) > '12:30')                 THEN CAST(CONVERT(varchar, ISNULL(Pe.EAdmitDateTime, PE.ADate), 112) AS varchar(50)) + '1'             ELSE NULL          END AS SNumber,          CONVERT(date, ISNULL(Pe.EAdmitDateTime, PE.ADate)) AS SStartDateTime,          CASE               WHEN (PE.EAdmitDateTime IS NOT NULL                     AND CAST(PE.EAdmitDateTime AS time) < '12:30')                    OR (CAST(PE.ADate AS time) < '12:30')                   THEN 'AM'              WHEN (PE.EAdmitDateTime IS NOT NULL                     AND CAST(PE.EAdmitDateTime AS time) >= '12:30')                    OR (CAST(PE.ADate AS time) > '12:30')                   THEN 'PM'              ELSE NULL          END AS CSession        FROM          (SELECT                PE1.PNumber,               PE1.REncounterId,               PE1.ADate,               PE1.EAdmitDateTime,               PE1.MType           FROM               (SELECT                    PNumber,                    REncounterId,                    ADate,                    EAdmitDateTime,                    MType                FROM                    PEncounter                WHERE                     PNumber IS NOT NULL) AS PE1           LEFT JOIN               PExtendedAttr PEA ON PE1.REncounterId = PEA.REncounterId              LEFT JOIN                PBooking PB ON PB.REncounterId = PE1.REncounterId) AS PE  

Here is the sample data from the database. When I run a query in the database, it returns the correct number of rows. The problem occurs only when I make a request through Postman or application UI.

TUniqueKey  Clinic  CSession           StartDateTime  ----------------------------------------------------  8CE5DE47    ABC1        AM               2021/03/11  56F3E7B2    ABC1        AM               2021/03/15  96E8BFE4    ABC1        AM               2021/03/10  97ACF31E    ABC1        PM               2021/03/11  22CAE64E    ABC1        PM               2021/03/15  B14DB9EA    ABC1        PM               2021/03/18  B14DB9EC    ABC1        AM               2021/03/18  

https://stackoverflow.com/questions/67410844/my-date-filter-is-not-working-properly-in-sql-server May 06, 2021 at 09:51AM