Table1:

I know we can use the trim and len functions to remove the last characters from column Name (remove 'smith') to show output as only 'Paul`.

How can we do the same for time in column Intime?

I tried

SELECT SUBSTRING('15:10:45.0486392',1,len('15:10:45.0486392')-8) AS Intime 
FROM stuattrecordAM

The output I got is "15:10:45" as expected.

SELECT SUBSTRING('Intime',1,len('Intime')-8) AS Intime 
FROM stuattrecordAM

Issue

When I try to use Intime column name instead the string it's showing the following error:

Argument data type time is invalid for argument 1 of substring function.

See also my previous, related question: How to trim Column Value SQL

How can I format the value so that result contains only first 8 characters displaying hours:minutes:seconds?

CodePudding user response：

Instead of TRIM and LEN, you can use a simpler function like REPLACE.

SELECT Value = REPLACE (Value, '''', '');

Do let me know if you still want to use TRIM and LEN.

CodePudding user response：

To get the hh:mm:ss portion of from a properly formatted time you can use the convert function, for example

declare @t varchar(16) = '15:10:45.0486392';
select Convert(char(8), @t, 114);

Result: 15:10:45