Блог

Существует одна конкретная дата, которую я не хочу включать в это, которая является «04/08/2020». Я хочу отфильтровать все записи с начала года до настоящего времени, но не 08.04.2020. Как я могу это сделать?

 SELECT 
     dbo.PID_RespiratorFit.PTIDNum,
     rtrim(acp.LastName)   ', '   rtrim(acp.FirstName) AS Name,
     acp.BadgeID AS ID,
     dbo.Patient.PcFLD01 as Organization,
     dbo.PID_RespiratorFit.Risk AS Exposure, 
     UPPER(dbo.PID_RespiratorFit.Type) AS Mask_Type,
     dbo.PID_RespiratorFit.OHSNotes AS Comments,
     dbo.PID_RespiratorFit.MedicallyClearedDate AS Medically_Cleared,
     dbo.PID_RespiratorFit.TrainingCompletedDate, 
     dbo.PID_RespiratorFit.DateIssued AS N95_Fittest_Completed, 
     dbo.PID_RespiratorFit.Mask AS N95_respirator,
     dbo.PID_RespiratorFit.PAPR,
     dbo.PID_RespiratorFit.PaprFitTestDate AS PAPR_Fittest_Completed,
     dbo.PID_RespiratorFit.Active,
     DATEADD(year, 1, dbo.PID_RespiratorFit.MedicallyClearedDate) AS Compliant_Through
FROM       dbo.PID_RespiratorFit LEFT JOIN 
           dbo.Patient ON dbo.PID_RespiratorFit.PTIDNum = dbo.Patient.PTIDNUM LEFT JOIN
           dbo.aspnet_CustomProfile acp ON dbo.Patient.PGuid = acp.UserId
WHERE (dbo.PID_RespiratorFit.DateIssued BETWEEN '1-1-2020' AND Getdate())
 

Используйте стандартные форматы дат! Затем:

 WHERE dbo.PID_RespiratorFit.DateIssued BETWEEN '2020-01-01' AND Getdate() and
      dbo.PID_RespiratorFit.DateIssued <> '2020-04-08'  -- or is that 2020-08-04???
 

Если дата действительно является датой / временем, то используйте:

 WHERE dbo.PID_RespiratorFit.DateIssued BETWEEN '2020-01-01' AND Getdate() and
      CONVERT(DATE, dbo.PID_RespiratorFit.DateIssued) <> '2020-04-08'  -- or is that 2020-08-04???
 

Я также настоятельно рекомендую использовать псевдонимы таблиц, чтобы запрос было намного проще записывать и читать. Например:

 SELECT . . .
FROM dbo.PID_RespiratorFit rf LEFT JOIN 
     dbo.Patient p
     ON rf.PTIDNum = p.PTIDNUM LEFT JOIN
     dbo.aspnet_CustomProfile acp
     ON p.PGuid = acp.UserId
    WHERE rf.DateIssued BETWEEN '2020-01-01' AND Getdate() AND
          CONVERT(DATE, rf.DateIssued) <> '2020-04-08'  -- or is that 2020-08-04???
 

Вы можете попробовать следующее — добавление дополнительного условия

     SELECT 
     dbo.PID_RespiratorFit.PTIDNum,
     rtrim(acp.LastName)   ', '   rtrim(acp.FirstName) AS Name,
     acp.BadgeID AS ID,
     dbo.Patient.PcFLD01 as Organization,
     dbo.PID_RespiratorFit.Risk AS Exposure, 
     UPPER(dbo.PID_RespiratorFit.Type) AS Mask_Type,
     dbo.PID_RespiratorFit.OHSNotes AS Comments,
     dbo.PID_RespiratorFit.MedicallyClearedDate AS Medically_Cleared,
     dbo.PID_RespiratorFit.TrainingCompletedDate, 
     dbo.PID_RespiratorFit.DateIssued AS N95_Fittest_Completed, 
     dbo.PID_RespiratorFit.Mask AS N95_respirator,
     dbo.PID_RespiratorFit.PAPR,
     dbo.PID_RespiratorFit.PaprFitTestDate AS PAPR_Fittest_Completed,
     dbo.PID_RespiratorFit.Active,
     DATEADD(year, 1, dbo.PID_RespiratorFit.MedicallyClearedDate) AS Compliant_Through
FROM       dbo.PID_RespiratorFit LEFT JOIN 
           dbo.Patient ON dbo.PID_RespiratorFit.PTIDNum = dbo.Patient.PTIDNUM LEFT JOIN
           dbo.aspnet_CustomProfile acp ON dbo.Patient.PGuid = acp.UserId
WHERE (dbo.PID_RespiratorFit.DateIssued BETWEEN '1-1-2020' AND Getdate())
AND cast(dbo.PID_RespiratorFit.DateIssued as date)<>'2020-04-08'