sql - 我的日期筛选器在 SQL Server 中无法正常工作

Question

这是我用来在我的 UI 中检索数据的视图。这样做的问题是，当我查看 之间2021-03-09' and '2021-03-17的数据时，它也会返回结果2021-03-18。

我调查过Postman它经过的日期，发现它发送的日期也像startDateTime%20ge%202021-03-08T12:00:00.000Z%20and%20startDateTime%20le%202021-03-17T12:00:00.000Z返回2021-03-18数据一样。我不希望它返回第 18 个。有没有办法在脚本中修改。但是如果搜索 2021-03-18 日期范围startDateTime%20ge%202021-03-17T12:00:00.000Z%20and%20startDateTime%20le%202021-03-18T12:00:00.000Z，它会返回正确的行数。

ALTER VIEW [dbo].[vw_o_sessions]
AS
    SELECT DISTINCT
        NEWID() AS TUniqueKey,
        SNumber AS Sessionid,
        CSession,
        SStartDateTime AS StartDateTime,
    FROM
        [vw_o_appointments]
    where ClinicCode='abc1'
   and SStartDateTime between '2021-03-09' and '2021-03-17'
    GROUP BY
        SSessionNumber,     
        CSession, 
        SStartDateTime, 

另一种观点：

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 convert(varchar(8), isnull(Pe.EAdmitDateTime, PE.ADate),112 ) + '0'
           WHEN (PE.EAdmitDateTime IS NOT NULL 
                 AND CAST(PE.EAdmitDateTime AS time) >= '12:30') 
                OR (CAST(PE.ADate AS time) > '12:30') 
              then convert(varchar(8), isnull(Pe.EAdmitDateTime, PE.ADate),112) + '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

这是来自数据库的示例数据。当我在数据库中运行查询时，它会返回正确的行数。仅当我通过Postman应用程序 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

标签： sqlsql-servertsql