How to convert date and/or time from character string as variable field

by jack   Last Updated June 13, 2019 09:06 AM

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 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 > DATEADD(day,-30,GETDATE())
 AND sdb.Name NOT IN ('DBA')
AND sdb.state_desc  = 'ONLINE'
--GROUP BY sdb.Name
)
INSERT INTO XXXXXXXXXXXXXXXXX
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


Related Questions


Updated February 14, 2019 22:06 PM

Updated February 08, 2019 19:06 PM

Updated February 12, 2018 10:06 AM

Updated September 11, 2018 22:06 PM

Updated March 14, 2017 04:06 AM