Convert to HH:MM in SQL Server

Question

I would like to convert a date time format to hh:mm using SQL Server.

For example: convert this value 2017-08-17 15:31:18.217 into 15:31.

If anyone knows how to do this, please help.

You can convert it to datetime or just use `SELECT SUBSTRING('2017-08-17 15:31:18.217',12,5)` — Marek Vitek, Aug 17 '17 at 10:39
If your value is stored as datetime, then this question is duplicate of [How to get Time from DateTime format in SQL?](https://stackoverflow.com/questions/7710449/how-to-get-time-from-datetime-format-in-sql) — Marek Vitek, Aug 17 '17 at 10:44

score 3 · Accepted Answer · answered Aug 17 '17 at 10:38

3

Here is one way to do it:

DECLARE @DateTime datetime = '2017-08-17 15:31:18.217'

SELECT CONVERT(char(5), @DateTime, 108)

Result: 15:31

answered Aug 17 '17 at 10:38

Zohar Peled

score 2 · Answer 2 · answered Aug 17 '17 at 10:52

2

SELECT FORMAT(cast('2017-08-17 15:31:18.217' as datetime),'hh:mm')

answered Aug 17 '17 at 10:52

piyush jain

5

While this code snippet may solve the question, [including an explanation](http://meta.stackexchange.com/questions/114762/explaining-entirely-code-based-answers) really helps to improve the quality of your post. Remember that you are answering the question for readers in the future, and those people might not know the reasons for your code suggestion – doydoy Aug 17 '17 at 11:14

Gagan Sharma · Answer 3 · 2017-08-17T11:50:17.487

0

Please execute these 2 queries , you will get the hours and minutes of current time.

 SELECT GETDATE() 
 SELECT CONVERT(VARCHAR(5), CAST(GETDATE() AS TIME))

edited Aug 17 '17 at 11:50

answered Aug 17 '17 at 10:37

Gagan Sharma

@Loki , Please execute these 2 queries , you will get the hours and minutes of current time. That's the requirement. SELECT GETDATE() SELECT CONVERT(VARCHAR(5), CAST(GETDATE() AS TIME)) – Gagan Sharma Aug 17 '17 at 11:47
Please edit this to the answer. Thus, future users can understand. Also, have a look at [how to answer](https://stackoverflow.com/help/how-to-answer) – loki Aug 17 '17 at 11:48
@Loki , Done, Thanks – Gagan Sharma Aug 17 '17 at 11:51

score 0 · Answer 4 · answered Aug 17 '17 at 10:42

0

SELECT CONVERT(VARCHAR(5), GETDATE(), 108)

You may want to use TRY_CONVERT if supported,and you don't always have a in that column.

One thing, the value that gets converted needs to be of type DATETIME.

answered Aug 17 '17 at 10:42

Horia

4 Answers4