Conversion of varchar to time failed in SQL Server 2014

Question

My table:

When I convert it to time, it fails while selecting from table:

But, when I test it in string, then there is no issue. What is the problem? I don't know.

Why are you storing time in a varchar? Read Aaron Bertrand's [Bad habits to kick : choosing the wrong data type](https://sqlblog.org/2009/10/12/bad-habits-to-kick-choosing-the-wrong-data-type) — Zohar Peled, May 10 '18 at 06:25

score 2 · Answer 1 · edited May 10 '18 at 07:51

2

I think you have new line in your time column. Please use substring to get exact time.

select CONVERT(time, substring(timout, 1, 5), 104) 
from tbl_test

Convert will create problem when time is null or invalid so use Len function.

select CONVERT(time, substring(timout, 1, 5), 104) 
from tbl_test  
where len(TimOut) > 4

edited May 10 '18 at 07:51

marc_s

answered May 10 '18 at 07:08

KHAIRULLAH REHAN

if length is greater than 4 or less than what ? – Khalid May 10 '18 at 07:15
you can use case statement. – KHAIRULLAH REHAN May 10 '18 at 07:29
then use case statement and convert the less than 4 digit time to that equall to 4 digit. like below answer – Abdul Jalil Marwat May 10 '18 at 07:33

score 2 · Accepted Answer · answered May 10 '18 at 07:34

2

select CONVERT(time, substring((CASE TimOut WHEN '0' THEN '00:00' ELSE TimOut END),1,5),104) from tbl_test

answered May 10 '18 at 07:34

2 Answers2