Get hour:minutes from DATETIME2

Question

I have a problem in SQL Server 2008 R2. I want to get HH:mm from a DATETIME2 column. When I write this statement:

SELECT CONVERT(VARCHAR(5), getdate(), 8)

I am getting perfect result that is 11:19, but when I try to get time from:

SELECT (CONVERT(VARCHAR(5), '1900-01-01 01:10:00.0000000', 8))

I am getting result 1900 but I need 01:10.

How can I get the result from this statement? I know there there are multiple questions/answers like this but I have not found any answer that matches my question.

you do realize that both statement are not equal. [1] is to convert datetime to string using style 8. [2] is to convert string to string using style 8 — Squirrel, Nov 12 '16 at 08:10

James · Answer 1 · 2016-11-12T07:00:29.013

2

Try this:

SELECT RIGHT('0' + CONVERT(VARCHAR(2), 
DATEPART(HOUR, '1900-01-01 01:10:00.0000000')), 2) hh, 
DATEPART(minute, '1900-01-01 01:10:00.0000000') mm

Or

SELECT LEFT(CONVERT(TIME(0), '1900-01-01 01:10:00.0000000'), 2) hh , DATEPART(minute, '1900-01-01 01:10:00.0000000') mm

edited Nov 12 '16 at 07:00

answered Nov 12 '16 at 06:01

James

729
6
10

this is returning 1:10 from your query but i want 01:10 – A.Goutam Nov 12 '16 at 06:03
thanks for your reply but Format support in 2012 sql server – A.Goutam Nov 12 '16 at 06:18
check now, i am having 2012 its hard to tell from my side – James Nov 12 '16 at 06:25

score 2 · Accepted Answer · answered Nov 12 '16 at 08:11

On SQL Server side (in stored procedure, for example), you can use this kind of conversion:

declare @dt datetime2(0) = sysdatetime();

select @dt as [Date], left(convert(time(0), @dt, 108), 5) as [HH:mm];

If you need to display your data on the client side, such as a report or your app, it's better to use client' formatting capabilities. This way, you may be able to take such factors into account as preferred calendar system, 12- or 24-hour clock, etc.

Hadi · Answer 3 · 2016-11-12T07:34:00.580

0

For many format of 24H datetime length is 19 so you can use the following

 SELECT RIGHT('0' + CAST(DATEPART(HOUR, Substring('1900-01-01 01:10:00.0000000',1,19)) AS VARCHAR(2)), 2) + ':' + CAST(DATEPART(minute, substring('1900-01-01 01:10:00.0000000',1,19)) AS VARCHAR(2)) HHmm

Hope it helps

edited Nov 12 '16 at 07:34

answered Nov 12 '16 at 07:17

Hadi

36,233
13
65
124

@H.Fadlaallah Conversion failed when converting the varchar value ':' to data type int – A.Goutam Nov 12 '16 at 07:27

score 0 · Answer 4 · answered Nov 12 '16 at 08:07

Try this, I think this is what you want.

this will give answer: 1:10

SELECT CONVERT(VARCHAR(5),CONVERT(DATETIME,'1900-01-01 01:10:00.000'),8)

You can also try this

select convert(VARCHAR,DATEPART(HOUR,'1900-01-01 01:10:00.0000000'))+':'+CONVERT(VARCHAR,DATEPART(MINUTE,'1900-01-01 01:10:00.0000000'))

Get hour:minutes from DATETIME2

4 Answers4