title: How can i format time from 09:00:00.0000000 to 9:00 in SQL Server?
body: <p>I have written the following query :</p>
  
  <pre><code>SELECT CASE WHEN rpwh.StartDayOfTheWeek = 1 THEN 7
  WHEN rpwh.StartDayOfTheWeek = 2 THEN 1 
  WHEN rpwh.StartDayOfTheWeek = 3 THEN 2 
  WHEN rpwh.StartDayOfTheWeek = 4 THEN 3 
  WHEN rpwh.StartDayOfTheWeek = 5 THEN 4 
  WHEN rpwh.StartDayOfTheWeek = 6 THEN 5 
  WHEN rpwh.StartDayOfTheWeek = 7 THEN 6
  END AS StartDayOfTheWeek,
  DATENAME(WEEKDAY, rpwh.StartDayOfTheWeek - 2) DayOfTheWeek,
  rpwh.StartAt, rpwh.EndAt
  FROM ResourceProfileWorkingHours rpwh
  JOIN ResourceProfile rp ON rpwh.ResourceProfileId = rp.Id
  WHERE rp.Alias = 'Accounts Payable'
  ORDER BY StartDayOfTheWeek
  </code></pre>
  
  <p>Which returns:</p>
  
  <pre><code>StartDayOfTheWeek   DayOfTheWeek    StartAt              EndAt
  1                   Monday          09:00:00.0000000     17:00:00.0000000
  2                   Tuesday         09:00:00.0000000     17:00:00.0000000
  3                   Wednesday       09:00:00.0000000     17:00:00.0000000
  4                   Thursday        09:00:00.0000000     17:00:00.0000000
  5                   Friday          09:00:00.0000000     17:00:00.0000000
  6                   Saturday        09:00:00.0000000     17:00:00.0000000
  7                   Sunday          09:00:00.0000000     17:00:00.0000000
  </code></pre>
  
  <p>What I need is for the StartAt time to be converted to 9:00 and the EndAt time to 17:00. I have tried writing the following line, but when I run it I get null values for both columns. How can i do this ?</p>
  
  <pre><code>FORMAT(rpwh.StartAt,'hh:mm') AS StartTime, FORMAT(rpwh.EndAt, 'hh:mm') AS EndTime
  </code></pre>