How to concatenate DateParts in Sql Server

Question

I really need a help from Database ninjas.

I have the following piece of code:

SELECT DATEPART(hh, BeginMinute) AS Intervalo, SUM(Qtde) AS Total
  FROM tr_CustomerCount
WHERE BeginMinute > '5/22/2013 00:00:00 AM' AND BeginMinute < '6/22/2013 12:00:00 AM'
GROUP BY DATEPART(hh, BeginMinute)

Actually it just returns the Hour (HH) but I wanna show the HOUR and MINUTE together separated by " : " such '12:00' its can be a String, no worries.

How can I do this?

Thanks in advance!

Answer 1

You could use CONVERT with style 114 (section Date and Time Styles):

SELECT CONVERT(VARCHAR(5), GETDATE(), 114);

or, starting from SQL Server 2012 you can use FORMAT (demo):

SELECT FORMAT(GETDATE() , 'hh:mm');

Answer 2

Example:

DECLARE @theDate DATETIME
SET @theDate = CURRENT_TIMESTAMP

SELECT @theDate 
PRINT (RIGHT('00' + convert(varchar(2), DATEPART(hour, @theDate)), 2) +
    ':' + RIGHT('00' + convert(varchar(2), DATEPART(minute, @theDate)), 2))

Explanation:

• DATEPART() returns a numeric value, so each call needs to be cast to a text/string type (varchar)
• String concatenation in T-SQL is just 'string 1' + 'string 2'
• To pad with leading zeroes, concat your value with 2 leading zeroes, then call RIGHT() to return the right-most 2 characters from the combined string