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'))

Erik Darling · Accepted Answer · 2024-10-27 14:06:21Z

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'
);

Stack Exchange Network

Remove NULL from T-SQL query output

1 Answer 1

probably

Linked

Hot Network Questions

Remove NULL from T-SQL query output

1 Answer 1

probably

Linked

Related

Hot Network Questions