Remove NULL from T-SQL query output

Question

Found this beautiful query online (cannot remember who to thank for it) and tried to modify it so that backup_finish_date with no value wold not output as NULL, but have not been able to figure it out. The query does exactly what I'd like to do (and learn to do on my own) with the only exception of the NULL output. I'd also like to format it into dd-mm-yyyy hh:mm

SELECT name ,
 recovery_model_desc as 'Recovery Model' ,
 state_desc as 'State',
 d AS 'Last Full Backup' ,
 i AS 'Last Differential Backup' ,
 l AS 'Last log Backup'
FROM ( SELECT db.name ,
 db.state_desc ,
 db.recovery_model_desc ,
 type ,
 backup_finish_date
 FROM master.sys.databases db
 LEFT OUTER JOIN msdb.dbo.backupset a ON a.database_name = db.name
 ) AS Sourcetable
 PIVOT
 ( MAX(backup_finish_date) FOR type IN ( D, I, L ) ) AS MostRecentBackup
 WHERE (name NOT IN ('master','msdb','model','tempdb'))

Answer 1

probably

Given the limited information and description, I'm guessing this is what you're looking for:

SELECT
 M.name,
 [Recovery Model] = 
 M.recovery_model_desc,
 [State] = 
 M.state_desc,
 [Last Full Backup] = 
 FORMAT(ISNULL(M.D, '19000101'), 'dd-MM-yyyy hh:mm'),
 [Last Differential Backup] = 
 FORMAT(ISNULL(M.I, '19000101'), 'dd-MM-yyyy hh:mm'),
 [Last log Backup] = 
 FORMAT(ISNULL(M.L, '19000101'), 'dd-MM-yyyy hh:mm')
FROM
(
 SELECT
 db.name,
 db.state_desc,
 db.recovery_model_desc,
 a.type,
 a.backup_finish_date
 FROM master.sys.databases AS db
 LEFT OUTER JOIN msdb.dbo.backupset AS a
 ON a.database_name = db.name
) AS Sourcetable
PIVOT
(
 MAX(backup_finish_date)
 FOR type IN
 (
 D,
 I,
 L
 )
) AS M --ostRecentBackup
WHERE name NOT IN
(
 N'master',
 N'msdb',
 N'model',
 N'tempdb'
);