CODEWITHSUNDEEP
sqlsql-server-2008
Vlad11
Vlad11

Reputation: 103

Remove nano seconds from "Time" SQL

I have the following code in SQL

select "Time" 
from Order

which returns - 09:10:11.0000000

How do I format this to give me just the hh:mm:ss ?

Upvotes: 0

Views: 1210

Answers (5)

Ambareesh Surendran
Ambareesh Surendran

Reputation: 508

You should convert the field with date/time Styles.

Styles 20,108 will do the job:

select CONVERT(VARCHAR, [time], 108) as 'Time' from Order
select CONVERT(VARCHAR, [time], 20) as 'Time' from Order

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138980

Cast the value to time(0). The value in parentheses specifies the fractional seconds precision.

select cast([Time] as time(0)) as [Time]

time (Transact-SQL)

Upvotes: 0

ahmet
ahmet

Reputation: 61

TIME_FORMAT(time, '%T') will help you.

SELECT TIME_FORMAT(time, '%T') AS newTime FROM Order

Upvotes: 1

ewanc
ewanc

Reputation: 1334

It depends on the SQL implementation, but for SQL Server you should probably use the CONVERT() function.

Alternatively you could perform a substring on what is currently returned, or even a combination of the two.

Upvotes: 0

Rahul Tripathi
Rahul Tripathi

Reputation: 172518

You can try like this:

SELECT CONVERT(VARCHAR, [time], 20) as 'Time' 
from Order

Upvotes: 1

Related Questions