Skip to content
Advertisement

How to transpose NULL value to 00:00

I have a view that gets data from an internet database. The view I created works, but when there is no data (NULL) I need it to return 00:00. How can I achieve this?

Using SSMS I am getting the starttime of booked resources. I already get them in the right way. But sometimes it returns NULL. I searched the internet and already tried to remedy this, but it won’t work.

SELECT 
    CASE WHEN COUNT(1) > 0 
            THEN SUBSTRING(CONVERT(VARCHAR, MIN(DATEADD(hour, - 1, dbo.GetEventCustomData(dbo.events.id, N'algemeneinfo_startvoorstelling'))), 108), 1, 5) 
            ELSE '00:00' 
    END AS Start, 
    CASE WHEN COUNT(1) > 0 
            THEN SUBSTRING(CONVERT(VARCHAR, MAX(dbo.GetEventCustomData(dbo.events.id, N'algemeneinfo_eindevoorstelling')), 108), 1, 5) 
            ELSE '00:00' 
    END AS Einde 
FROM
    dbo.events 
INNER JOIN 
    dbo.EventScheduleEntry ON dbo.events.id = dbo.EventScheduleEntry.eventid 
INNER JOIN 
    dbo.Locations ON dbo.events.locations = dbo.Locations.id 
INNER JOIN 
    dbo.Profiles ON dbo.events.profile = dbo.Profiles.id 
WHERE 
    (dbo.Locations.name = N'studio') 
    AND (dbo.EventScheduleEntry.starttime > CONVERT(date, GETDATE())) 
    AND (dbo.EventScheduleEntry.starttime < CONVERT(date, DATEADD(day, 1, GETDATE()))) 
    AND (dbo.events.status = N'bevestiging') 
    AND (dbo.Profiles.name <> N'buffer')        

I expect when the view returns NULL, that it returns 00:00 instead.

Advertisement

Answer

As stated in the comments you should use ISNULL function, like this:

SELECT  ISNULL(SUBSTRING(CONVERT(varchar, MIN(DATEADD(hour, - 1, dbo.GetEventCustomData(dbo.events.id, N'algemeneinfo_startvoorstelling'))), 108), 1, 5), '00:00')
            AS Start, 
        ISNULL(SUBSTRING(CONVERT(varchar, MAX(dbo.GetEventCustomData(dbo.events.id, N'algemeneinfo_eindevoorstelling')), 108), 1, 5), '00:00')
            AS Einde 
FROM dbo.events 
INNER JOIN dbo.EventScheduleEntry 
    ON dbo.events.id = dbo.EventScheduleEntry.eventid 
INNER JOIN dbo.Locations 
    ON dbo.events.locations = dbo.Locations.id 
INNER JOIN dbo.Profiles 
    ON dbo.events.profile = dbo.Profiles.id 
WHERE (dbo.Locations.name = N'studio') 
AND (dbo.EventScheduleEntry.starttime > CONVERT(date, GETDATE())) 
AND (dbo.EventScheduleEntry.starttime < CONVERT(date, DATEADD(day, 1, GETDATE()))) 
AND (dbo.events.status = N'bevestiging') 
AND (dbo.Profiles.name <> N'buffer')   

User contributions licensed under: CC BY-SA
8 People found this is helpful
Advertisement