How to get alone time 11:21 (highlighted in red) with SQL Server database?

Question

Done automatically add the date to the time of the database SQL Server 2005 Express. So when you add a new record in the database is added automatically date and time in the form of:

Create Table Baza_test
(

   ID bigint IDENTITY (1,1) NOT NULL,
   Client nvarchar (23)
   address nvarchar (46)
   DateOfAddmission datetime default CURRENT_TIMESTAMP
   aaaaaadres nvarchar (46)

);

How to get alone time (highlighted in red):

with SQL Server database?

Answer 1

With the following query:

select CONVERT(VARCHAR(5), DateOfAddmission,108) AS [time] from Baza_test where ID = 1;

Answer 2

Try like this,

12 HOURS FORMAT

SELECT LTRIM(RIGHT(CONVERT(VARCHAR(20), DateOfAddmission, 100), 7)) FROM  Baza_test

24 HOURS FORMAT

SELECT CONVERT(VARCHAR(5),DateOfAddmission, 108) FROM  Baza_test

Answer 3

There is already similar question out there, please refer to the link bellow:

https://stackoverflow.com/a/7710495/4558361 by t-clausen.dk

Answer 4

The CONVERT(VARCHAR) answers are valid, if you want more flexibility and don't know the conversion codes like me :), you could do it with FORMAT:

DECLARE @d DATETIME = GETDATE();  
SELECT FORMAT( @d, 'hh:mm') AS 'Time Result'

Answer 5

Apart from the answer given here another way is this -

SELECT CAST(CAST(GETDATE() AS Time(0)) AS VARCHAR(5))

Output

05:48

And in your case its -

SELECT CAST(CAST(DateOfAddmission AS Time(0)) AS VARCHAR(5)) AS [time] 
FROM Baza_test 
WHERE ID = 1;

Answer 6

If 2012+

Select format(GetDate(),'HH:mm')    --For the 24 clock
Select format(GetDate(),'hh:mm tt') --For the 12 clock  

That said, I was informed that FORMAT() has a poor performance