CODEWITHSUNDEEP
sqldatesql-server-2008filterbetween
Ph14 Woozies
Ph14 Woozies

Reputation: 17

Not include this specific date in the query when using BETWEEN operator in SQL Server 2008

There is one specific date that I do not want to include in this which is "04/08/2020". I want to filter all the records from the beginning of the year till now but not 04/08/2020. How can I do that?

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())

test

Upvotes: 0

Views: 54

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269493

Use standard date formats! Then:

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???

If the date is really a date/time, then use:

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???

I also strongly recommend table aliases so the query is much easier to write and read. For example:

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???

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

You can try the below - adding an extra condition

    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'

Upvotes: 1

Related Questions