3

Here's what I use to get a HH:MM string:

CONVERT(varchar(5), time_requested, 108)

I'm thinking there may be a more elegant way, even maybe more efficient (see similar question on How to remove the time portion of a datetime value (SQL Server)?).

Requirements:

  • the final result has to be easily convertible to a string (in order to be able to concatenate some field time_created with a field such as date_created).
  • the following 2 cases must be covered: HH:MM and HH:MM:SS.
Community
  • 1
  • 1
user3341592
  • 1,419
  • 1
  • 17
  • 36

2 Answers2

5
Declare @D datetime = GetDate()      -- Data Type could be Time as well

Select CONVERT(varchar(8), @D, 108)  -- for HH:MM:SS  11:27:26
Select CONVERT(varchar(5), @D, 108)  -- for HH:MM     11:27

Failed to mention, if 2012+ you could also use Format()

Declare @D DateTime = '2016-10-22 13:30:25'

Select Format(@D,'HH:mm')         -- 13:30
Select Format(@D,'hh:mm:ss tt')   -- 01:30:25 PM
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66
4

Your method is fine. It produces a string representation of the value.

You can also convert to a time data type:

select cast(time_requested as time)

But note that this is "format-less" -- it contains hours, minutes, seconds, and fractions of seconds. So, your method is probably the better approach.

Nimantha
  • 6,405
  • 6
  • 28
  • 69
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786