SQL SELECT HH:MM - HH:MM

Question

I am trying to write an SQL query that will return the current time as follows:

HH:MM - HH:MM (+1)

E.g. if the time is 14:00 it would return 14:00 - 15:00

I have managed to get as far as follows:

SELECT TOP (30) 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, QUEUE_TIME.QueueDate), 0) AS 'DateAdded',
    CAST(CONVERT(VARCHAR(2), DATEPART(HH, QUEUE_TIME.QueueDate), 108) AS VARCHAR(2)) + ' - ' + 
    CAST(CONVERT(VARCHAR(2), DATEPART(HH, QUEUE_TIME.QueueDate), 108) + 1 AS VARCHAR(2)) AS Interval, 
    QUEUE_TYPE.Name, QUEUE_TIME.QueueTypeId, 
    MAX(QUEUE_TIME.QueuedTimeInSec / 60) AS 'WaitingTimeInSec', 
    CAST(ROUND(AVG(QUEUE_TIME.FlowRateWhenJoinedPerMin), 1) AS numeric(36, 2)) AS AvgFlowRate
FROM
    QUEUE_TIME 
INNER JOIN
    QUEUE_TYPE ON QUEUE_TIME.QueueTypeId = QUEUE_TYPE.Id
WHERE
    (QUEUE_TIME.QueueDate >= '11/07/2014 00:00') 
    AND (QUEUE_TIME.IsFreeFlowing = '0') 
    AND (QUEUE_TIME.QueueTypeId = '3')
GROUP BY 
    DATEADD(HOUR, DATEDIFF(HOUR, 0, QUEUE_TIME.QueueDate), 0), 
    DATEPART(HOUR, QUEUE_TIME.QueueDate), QUEUE_TYPE.Name, QUEUE_TIME.QueueTypeId
ORDER BY 
    'DateAdded'

This will return as follows, e.g. if it is 8am it will return 8-9. But i need it to be in the format 08:00 - 09:00.

Any assistance would be appreciated.

I am using SQL Server 2008.

Thanks

Answer 1

This is an example, you can replace @now variable with column name:

declare @now datetime = getdate()

select convert(varchar(5), @now, 114) + ' - ' + 
       convert(varchar(5), dateadd(hour, 1, @now), 114) yourColumn

SQL Fiddle demo

Answer 2

Try with FORMAT for SQL Server 2012+:

SELECT FORMAT(GETDATE(), 'HH:mm') + '-' + FORMAT(DATEADD(HH, 1, GETDATE()), 'HH:mm')