How to convert date and/or time from character string as variable field I have a backup report which looks to see if all databases have been backed up within the last 30 days, and want to insert 'Not all databases backed up in last 30 days' into that field (datetime type) if it is the case that not all databases have been backed up within the last 30 days but i get the error Conversion failed when converting date and/or time from character string.

;WITH CTE AS
(
SELECT 
--sdb.Name AS DatabaseName,
MAX(backup_finish_date) AS LastBackUpTime
FROM sys.databases sdb
LEFT OUTER JOIN msdb.dbo.backupset bus ON bus.database_name = sdb.name
WHERE bus.type = 'D'AND bus.database_name NOT IN ('master', 'model', 'msdb', 'tempdb') AND bus.backup_finish_date &gt; DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO Server.Database.Schema.TableName 
SELECT @@SERVERNAME  as serverName,
        --DatabaseName as DatabaseName,
        CASE WHEN LastBackupTime IS NULL THEN 'Not all databases backed up in last 30 days' ELSE MIN(LastBackUpTime) END as LastBackUpTime

FROM CTE
GROUP BY LastBackUpTime
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)