I am trying to execute my query but to only pull the dates for 2020, when I add a DatePart (Year, 2020) I do not get any results, although there are 2020 results. The format of the data from the system is YEAR-MONTH-DAY HR:MIN:SEC:SEC. can anyone direct me where I am going wrong?
SELECT CV3ClientVisit.ClientDisplayName, CV3ClientVisit.CurrentLocation, CV3ClientVisit.IDCode, CV3ClientVisit.VisitStatus, CV3Order.Name, CV3ClientVisit.TypeCode,
CV3User.DisplayName AS [Performed By], CV3OrderTaskOccurrence.PerformedFromDtm AS [Performed When], CV3OrderTaskOccurrence.TouchedBy AS [Cosigned By],
CV3OrderTaskOccurrence.TouchedWhen AS [Cosigned When]
FROM CV3Client INNER JOIN
CV3TaskSignature ON CV3Client.GUID = CV3TaskSignature.ClientGUID INNER JOIN
CV3ClientVisit INNER JOIN
CV3Order ON CV3ClientVisit.GUID = CV3Order.ClientVisitGUID INNER JOIN
CV3OrderTaskOccurrence ON CV3Order.GUID = CV3OrderTaskOccurrence.OrderGUID INNER JOIN
CV3TaskStatusHistory ON CV3OrderTaskOccurrence.GUID = CV3TaskStatusHistory.OrderTaskOccurrenceGUID ON CV3Client.GUID = CV3Order.ClientGUID INNER JOIN
CV3User ON CV3OrderTaskOccurrence.PerformedProviderGUID = CV3User.GUID
WHERE (CV3ClientVisit.TypeCode = 'Inpatient Admit') AND (CV3Order.Name = 'insulin lispro sliding scale injectable') AND (CV3ClientVisit.VisitStatus = 'ADM') AND
(CV3OrderTaskOccurrence.TouchedWhen = DATEPART(YEAR, 2020))
If touchedwhen
is a date/time, then use something like this:
(CV3OrderTaskOccurrence.TouchedWhen >= '2020-01-01' and
CV3OrderTaskOccurrence.TouchedWhen < '2021-01-01'
)
Actually, this will also work if the value is stored as a string, but I strongly recommend that you store values using correct data types.