Look up CAST AND CONVERT in Books Online. But be aware that this changes the datatype to a string, and should be used for output formatting only. And it is preferable to let your interface or reporting tool handle formatting of output.
Why do you think you need to convert it to short date? Are you trying to truncate the value?
If it's not practically useful, then it's practically useless.
Well, it is ASKED hourly, but just wanted to truncate it to daily or weekly for my post.
You make things so complicated. Why didn't you just say that today the question will be asked at:
create table Numlist (num int identity(1,1) not null primary key)
insert Numlist default values
while scope_identity() < 24 insert numlist default values
select dateadd(hh, num, '10/4/2005') from numlist
drop table numlist
SELECT DateAdd(hour, o0 + o1 * 8, dateadd(d, datediff(d, 0, GetDate()), 0))
FROM (SELECT 0 AS o1 UNION SELECT 1 UNION SELECT 2) AS a
CROSS JOIN (SELECT 0 AS o0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3
UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS b
Bonus points for the first person to describe what bit of deviance led to my choices of values (pre-"Release V" users have an advantage here).