How to Split 24 hours into hour slab in Sql Server '1-2 AM
2-3 AM
3-4 AM 
4-5 AM
5-6 AM
6-7 AM
7-8 AM
8-9 AM
9-10 AM
10-11 AM
11-12 AM
12-1  PM
till 
11-12 PM'


Total 24 row show.

Parameter is date like='07/06/2017'

what i am trying...

(convert(varchar,case when DATEPART(HOUR, START_TIME)%12&lt;&gt;0 then DATEPART(HOUR, START_TIME)%12 else 12 end) +' - '+ convert(varchar,DATEPART(HOUR, START_TIME)%12+1) + case when DATEPART(HOUR, START_TIME)&gt;=12 then ' PM' else ' AM' end)




result is as per my needs but when any hours have no data then these hour not in output.
I want all hour slab whether that hour have data or not.

According to above output,there are shown only two hour's slot because only these hour's have a data.
So if I'm right you always want to return 24 rows, one for each hour slot, regardless of data existing for that slot?  I assume you only have one row per hour slot in your data?

Based on this answer here, create a table with a row for each hour slot then outer join it to your results table, which will always give you a time row regardless of rows existing in your table

WITH Hours AS
(
  SELECT 0 as hour
  UNION ALL
  SELECT  hour + 1
  FROM    Hours   
  WHERE   hour + 1 &lt; 24
)
SELECT  CONVERT(varchar, h.hour) + ' - ' + CASE WHEN h.hour + 1 = 24 THEN 'Midnight' ELSE CONVERT(varchar, h.hour + 1) END + CASE WHEN h.Hour &gt;=12 THEN ' PM' ELSE ' AM' END, y.*
FROM    Hours h LEFT JOIN yourtable y ON DATEPART(HH, y.START_TIME) BETWEEN h.hour AND h.hour + 1  


You will need to add a clause to the join to restrict yourtable to one days data

Note: you could replace the table in the WITH statement with a real table containing 24 rows, one for each hour if you want.