How to convert date and/or time from character string as variable field
The below should return 'Not all databases backed up' if any of the databases haven't been backed up, and 'All databases backed up as of DD/MM/YYYY' if they have (this will be the highest backup date found)

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
ISNULL(MAX(backup_finish_date),0) AS LastBackUpTime,
[sdb].[name] AS DatabaseName
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE (bus.type = 'D' OR bus.[database_name] IS NULL)
AND sdb.name NOT IN ('master', 'model', 'msdb', 'tempdb','DBA')
AND sdb.state_desc  = 'ONLINE'
GROUP BY sdb.Name
)

SELECT 
    CASE
        WHEN MIN([CTE].[LastBackUpTime]) IS NULL THEN 'Not all databases backed up'
        WHEN MIN([CTE].[LastBackUpTime]) &lt;= DATEADD(dd,-30,GETDATE()) THEN 'Not all databases backed up'
        ELSE 'All databases backed up as of ' + CAST(MAX([CTE].[LastBackUpTime]) AS NVARCHAR(100))
    END
FROM CTE


The script looks for the highest backup date for all non-system (and non-DBA) databases, and returns the datetime equivalent of 0 if there are none (this converts to 1900-01-01 00:00:00.000). We can then check whether the minimum date is in our 30 day region or not.

The actual issue you were experiencing was because your CASE statement returned both a DATETIME data type and an VARCHAR data type - because of data type precedence for a CASE statement SQL server will always choose the data type of the highest precedence as the data type of that field - in this case that was a DATETIME.

To prevent this we simply need to CAST the DATETIME field as a VARCHAR, which prevents the implicit conversion from VARCHAR to DATETIME (lower to higher precedence)