SEARCH Employee table based on EXACT Date of Birth or PARTIAL Date of Birth

Hi Douang,

There is an Implicit Conversion done when comparing YEAR(vp.DOB) to @DOB_YYYY. Query Engine will try converting YEAR(vp.DOB) into datetime before comparing to @DOB_YYYY, which will never be correctly matched.

In addition, when '1960' is assigned to @DOB_YYYY, its value is actually '1960/01/01'.

May I suggest changing the datatype of @DOB_YYYY, @DOB_MM and @DOB_DD from DATETIME to INT?

DECLARE @DOB DATETIME
DECLARE @DOB_YYYY INT
DECLARE @DOB_MM INT
DECLARE @DOB_DD INT

SET @DOB = NULL
SET @DOB_YYYY = 1960
SET @DOB_MM = NULL
SET @DOB_DD = NULL

 
SELECT DISTINCT TOP (5000) 

  dbo.fnGetDisplayName(vp.LastOrBusinessName,vp.FirstName, vp.MiddleName, vp.SuffixName) as [Name],
  dbo.fnGetDisplayNameByPartyID(vp.FirstPlaintiffID) as Plaintiff

FROM
        tblEmployee vp WITH (NOLOCK) 
          
WHERE 
-- Search by DOB
(vp.DOB = @DOB OR @DOB IS NULL)
AND
(YEAR(vp.DOB) = @DOB_YYYY OR @DOB_YYYY IS NULL)
AND 
(MONTH(vp.DOB) = @DOB_MM OR @DOB_MM IS NULL)
AND 
(DAY(vp.DOB) = @DOB_DD OR @DOB_DD IS NULL)

Hope this helps.

~J.