CODEWITHSUNDEEP
sql-server
felipekm
felipekm

Reputation: 2910

How to concatenate DateParts in Sql Server

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!

Upvotes: 5

Views: 38246

Answers (4)

Nicholas Carey
Nicholas Carey

Reputation: 74197

This is all awfully complicated for something trivial. Why don't you simply try:

left(convert(varchar,current_timestamp,108),5)

which converts a datetime value to string represention of hours and minutes on a 24 hour clock, so a time of, say, 2:37 PM is converted to the string value 14:37.

Once you have that, then...

select time_hhmm = left(convert(varchar,BeginMinute,108),5) , 
       total     = sum(Qtde)
from tr_CustomerCount
where BeginMinute > '5/22/2013 00:00:00 AM'
  and BeginMinute < '6/22/2013 12:00:00 AM'
group by left(convert(varchar,BeginMinute,108),5)

Much easier on the eyes.

Amended to reflect showing only HH:00 per OP's comment:

select time_hhmm = right( '0'+datename(hour,BeginMinute) , 2 ) + ':00 , 
       total     = sum(Qtde)
from tr_CustomerCount
where BeginMinute > '5/22/2013 00:00:00 AM'
  and BeginMinute < '6/22/2013 12:00:00 AM'
group by datename(hour,BeginMinute)

Upvotes: 1

davmos
davmos

Reputation: 9577

SELECT RIGHT('00' + rtrim(cast(DATEPART(hh, BeginMinute) as varchar)), 2) + ':' + 
       RIGHT('00' + rtrim(cast(DATEPART(mi, BeginMinute) as varchar)), 2) 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), DATEPART(mi, BeginMinute)

Click here to see it in action at SQL Fiddle

Upvotes: 1

Bogdan Sahlean
Bogdan Sahlean

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

Upvotes: 17

DaveD
DaveD

Reputation: 2255

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

Upvotes: 1

Related Questions