CODEWITHSUNDEEP
t-sqltime
coblenski
coblenski

Reputation: 1189

SQL Display time hours:minutes only

I'm converting an int to time using the following code, but the result comes out as 09:00:00.000000 etc.

(cast(CAST(t0.U_ProdStartTime / 100 as varchar) + ':' + CAST(t0.U_ProdStartTime % 100 as varchar) 
as time)) 
AS 'Start Time',

(cast(CAST(t0.U_ORC_BE_ProdTime / 100 as varchar) + ':' + CAST(t0.U_ORC_BE_ProdTime % 100 as varchar) 
as time))
AS 'End Time',

I want it to only display 09:00:00?

Upvotes: 0

Views: 2519

Answers (4)

Aruna Prabhath
Aruna Prabhath

Reputation: 242

you can use the 'FORMAT' to do this easily

 FORMAT(GETDATE(), 'HH:mm')

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 13700

This is one method I know

declare @t int
set @t=345
select convert(varchar(10),
(cast(CAST(@t / 100 as varchar) + ':' + CAST(@t % 100 as varchar) 
as datetime)) ,108)
AS 'Start Time'

Result

Start Time
----------
03:45:00

Upvotes: 0

Milutin Cirkovic
Milutin Cirkovic

Reputation: 53

SELECT FORMAT(t0.U_ProdStartTime,'hh:mm') AS StartTime,FORMAT(t0.U_ORC_BE_ProdTime,'hh:mm') AS EndTime 
FROM YourTable AS [to]

or if u need seconds u can use 

 SELECT FORMAT(t0.U_ProdStartTime,'hh:mm:ss') AS StartTime,FORMAT(t0.U_ORC_BE_ProdTime,'hh:mm:ss') AS EndTime 
 FROM YourTable AS [to]

Upvotes: 0

Mahdi Aslami Khavari
Mahdi Aslami Khavari

Reputation: 1985

CONVERT(VARCHAR(10), GETDATE(), 108)

Upvotes: 4

Related Questions