sql cast hour out of datetime without dropping leading zero on single digit hours

Question

CAST(DATEPART(hh, timestamp) AS varchar(2)) + ':00' AS Hour

This will get me the hour out of a timestamp field but in the case of the hours 0-9 it does not pad a leading zero and therefore when I sort by hour descending it does not sort correctly.

Not sure what is wrong here. I specify a 2 char varchar to allow extra room for the leading zero. Hopefully there is a way to fix this without passing my field through a function that will pad the leading zero for me.

Answer 1

SELECT RIGHT('0' + CONVERT(VARCHAR(2), DATEPART(HOUR, GETDATE())), 2) + ':00';

Don't want to pad with 0s? OK, you can do it a similarly ugly way without the padding mess:

SELECT LEFT(CONVERT(TIME(0), GETDATE()), 2) + ':00';

Obviously replace GETDATE() with your column name. Which I hope isn't really timestamp because this is a reserved word - for a data type that has nothing to do with date or time, sadly.

If you don't like either of those solutions, then just select the data from SQL Server and let your client application handle formatting/presentation details. Surely this is easy to do with C#'s format() function, for example.

Answer 2

Assuming timestamp is a column in your table

select convert(char(3), timestamp, 108) + '00' AS Hour 
from yourtable

Alternative

select left(cast(dateadd(hh, datediff(hh, 0, timestamp), 0) as time), 5)
from yourtable

Edit:

After testing a bit i came to the conclusion that this is the fastest way (almost the performance and syntax as Aaron's solution)

SELECT RIGHT(100 + DATEPART(HOUR, timestamp) , 2) + ':00'
from yourtable

Answer 3

You can use the TIME type which is 24 hours by default;

cast(cast(timestamp as time) AS varchar(2)) + ':00' AS Hour