Syntax error converting datetime from character string

[JBourne77]

Please note, the SQL is handled dynamically by the server, therefore some of the items in my WHERE clause will look odd to you, please disregard this. I am getting the following error in my query. Any assistance is appreciated.

Msg 241, Level 16, State 1, Line 132
Syntax error converting datetime from character string.

My Query:

/* Patient List*/
SET NOCOUNT ON

DECLARE @Zip VARCHAR(40)
SELECT
    @Zip = LTRIM(RTRIM('NULL')) + '%'

SELECT
    PatientID,
    RespSameAsPatient = ISNULL(PatientSameAsGuarantor , 0),
    PatientName = CASE WHEN RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix , '')) <> '' THEN RTRIM(RTRIM(pp.Last + ' ' + ISNULL(pp.Suffix , '')) + ', ' + ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , ''))
                       ELSE RTRIM(ISNULL(pp.First , '') + ' ' + ISNULL(pp.Middle , ''))
                  END,
    PatientAddr1 = pp.Address1,
    PatientAddr2 = pp.Address2,
    PatientCity = pp.City,
    PatientState = pp.State,
    PatientZip = pp.Zip,
    FLOOR(DATEDIFF(DAY , pp.birthdate , GETDATE()) / 365.25) AS [Patient Age],
    PatientRespName = CASE WHEN RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix , '')) <> '' THEN RTRIM(RTRIM(pr.Last + ' ' + ISNULL(pr.Suffix , '')) + ', ' + ISNULL(pr.First , '') + ' ' + ISNULL(pr.Middle , ''))
                           ELSE RTRIM(ISNULL(pr.First , '') + ' ' + ISNULL(pr.Middle , ''))
                      END,
    PatientRespAddr1 = pr.Address1,
    PatientRespAddr2 = pr.Address2,
    PatientRespCity = pr.City,
    PatientRespState = pr.State,
    PatientRespZip = pr.Zip,
    FinancialClass = ISNULL(ml.Description , 'none'),
    Doctor = df.ListName,
    Facility = df1.OrgName,
    Balance = ISNULL(ppa.PatBalance , 0) + ISNULL(ppa.InsBalance , 0),
    pp.DeathDate,
    CASE WHEN ISNULL(pp.Inactive , 0) = 0 THEN 'No'
         ELSE 'Yes'
    END AS Inactive,
    pp.BirthDate,
    ISNULL((
             SELECT TOP 1
                CONVERT(VARCHAR(20) , visit , 120)
             FROM
                patientvisit pv
             WHERE
                visit >= ISNULL(NULL , '1/1/1900') AND
                visit < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000')) AND
                pp.patientprofileid = pv.PatientProfileID AND
                DATEDIFF(day , GETDATE() , visit) <= 0
             ORDER BY
                visit DESC
           ) , 'No Last Visit') AS [Last Visit Date]
INTO
    #Tmp
FROM
    PatientProfile pp 
JOIN 
    PatientProfileAgg ppa ON pp.PatientProfileID = ppa.PatientProfileID 
LEFT JOIN Guarantor pr ON pp.GuarantorID = pr.GuarantorID 
LEFT JOIN MedLists ml ON pp.FinancialClassMID = ml.MedListsID 
LEFT JOIN DoctorFacility df ON pp.DoctorID = df.DoctorFacilityID 
LEFT JOIN DoctorFacility df1 ON pp.FacilityId = df1.DoctorFacilityID
WHERE
    --Filter on Patient
    (
      (
        NULL IS NOT NULL AND
        pp.PatientProfileID IN ( NULL )
      ) OR
      ( NULL IS NULL )
    ) AND
    -- Filter Inactive Patients
	(
        (
          0 = 0 AND
          ISNULL(pp.Inactive , 0) = 0
        ) OR
        ( 0 = 1 )
      ) AND
    -- Filter Deceased Patients
	(
        (
          0 = 0 AND
          pp.DeathDate IS NULL
        ) OR
        ( 0 = 1 )
      ) AND
    --Filter on Doctor
	(
        (
          NULL IS NOT NULL AND
          pp.DoctorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Financial Class
	(
        (
          NULL IS NOT NULL AND
          pp.FinancialClassMId = NULL
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Guarantor
	(
        (
          NULL IS NOT NULL AND
          pp.GuarantorID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Facility
	(
        (
          NULL IS NOT NULL AND
          pp.FacilityID IN ( NULL )
        ) OR
        ( NULL IS NULL )
      ) AND
    --Filter on Zip Code
	(
        (
          NULL IS NOT NULL AND
          1 = 1 AND
          pp.zip LIKE @Zip
        ) OR
        (
          NULL IS NOT NULL AND
          1 = 2 AND
          pr.zip LIKE @Zip
        ) OR
        ( NULL IS NULL )
      )

SELECT
    *
FROM
    #Tmp
WHERE
    --Filter on Age
    (
      ([Patient Age] >= ( '-1' ) AND
      [Patient Age] <= ( '125' ))
    ) AND
    -- Filter on Last Visit Date
 	(
        [Last Visit Date] >= ISNULL(NULL , CONVERT(DATETIME , '01/01/1900' , 101)) AND
        [Last Visit Date] < DATEADD(d , 1 , ISNULL(NULL , CONVERT(DATETIME , '01/01/3000' , 101)))
      )

DROP TABLE #Tmp

 

[markros]

This code looks strange to me

visit >= ISNULL(NULL , '1/1/1900') AND visit < DATEADD(d , 1 , ISNULL(NULL , '1/1/3000')) AND pp.patientprofileid = pv.PatientProfileID AND

The code

ISNULL(NULL , '1/1/1900') doesn't return a date field.

 

[markros]

It is always a good idea to explicitly convert value to the needed type. Check this blog
How to implement a typeof operator in SQL by using sql_variant_property