Cast a varchar field with mixed time format to time (0)

Question

Hi

I have a [time] field- varchar datatype and it has got values in mixed formats as below

14:24:00
07:00.0
10:00:56.7730000
54:80.0 (exclude wrong format)

I am trying to cast the field as time (0) but it is causing conversion error.
Can some one please help me with sort this out?

Thanks

Answer 1

Try identifying the possible formats and interpretations. For example, for shown data, try something like this:

select *, 
    try_cast(case when MyColumn like '[0-9][0-9]:[0-9][0-9].[0-9]%' then '00:' + MyColumn else MyColumn end as time(0)) as [Time]
from MyTable

Show details if there are more cases.

Answer 2

Hi @jennifer zen ,

Welcome to Microsoft Q&A!

Please also refer below:

declare @t table  
([time] varchar(max))  
  
insert into @t values  
 ('14:24:00'),  
('07:00.0'),  
('10:00:56.7730000'),  
('54:80.0')  
  
 select try_convert(time(0),IIF([time] like '[0-9][0-9]:[0-9][0-9].[0-9]' ,LEFT([time],5)+':00', [time])) as [Time]  
 from @t  

Output:

Time  
14:24:00  
07:00:00  
10:00:57  
NULL  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.