select only hours from date

Question

The below query returns value in this format 2017-11-18 16:00:00.000

How can i return value in hours only

Desired format

16:00:00.000
17:00:00.000
18:00:00.000

Query

SELECT DATEADD(Hour, DATEDIFF(Hour, 0, GetDate()), 0)

this will returns 16:50:16.6800000 i want to returns only hours of the day — ayman, Nov 18 '17 at 12:50
Possible duplicate of [Time part of a DateTime Field in SQL](https://stackoverflow.com/questions/12354699/time-part-of-a-datetime-field-in-sql) — Imran Ali Khan, Nov 18 '17 at 12:56

score 3 · Accepted Answer · answered Nov 18 '17 at 12:53

You can convert the value to time:

SELECT CONVERT(TIME, DATEADD(Hour, DATEDIFF(Hour, 0, GetDate()), 0))

I would be more inclined to write the logic as:

SELECT dateadd(hour, datepart(hour, getdate()), cast('00:00' as time))

There really is no difference . . . just the logic of adding a certain number of hours to '00:00' seems simpler.

1 Answers1